How to make multiple counts in one query?

Question

I count records with queries like

SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%word%'

For each count, mysql needs to walk thoughout the table, and this is a big problem if having long table and numerous queries.

I wonder if there is a way to make all counts in one query. In this case, when mysql walks over each row, it will process all counts, and no need to scanning the entire table over and over again.

Answer 1

To get a count for each of those you can try

SELECT
    COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1,
    COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2,
    COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3
FROM `table1`; 

Answer 2

Similar to Aaron's solutio, shorter syntax:

SELECT
    SUM(col1 LIKE '%something%') AS count1,
    SUM(col1 LIKE '%another%') AS count2,
    SUM(col1 LIKE '%word%') AS count3
FROM `table1`

The LIKE expression makes for a boolean result. TRUE is 1, FALSE is 0, so the CASE is redundant here.

Answer 3

If I get your need right this will perhaps this will do the trick:

SELECT SUM(CASE 
  WHEN col1 LIKE '%something' THEN 1 
  WHEN col1 LIKE '%another%' THEN 1 
END) AS result
FROM table1;