CPU utilization dangerously high due to COUNT queries Our app has recently been receiving traffic up to 10-30 requests per sec. The life cycle of every request is pretty complex. MySQL's load average ranges from 5 (when the requests are 10 per sec) and goes to 20 (30 requests, peak traffic, lasts for a few minutes).

The load average is abysmally high due to extremely high CPU utilization of 200-350%. Now, for every request that we receive, we fire off 3 COUNT queries on a single table. They look pretty similar.Just to show you how one looks like:

SELECT COUNT(DISTINCT COL1,COL2)
FROM conversion_Details c 
WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')
AND (COL4 = 0 OR COL4 = 17)
AND COL5=5
AND COL2 = 'SOMETHING' 


As you can see, for 1 count query, we have 1 table and 5 columns involved.
In total,we have 3 varchar columns, 2 int columns, and 1 timestamp column.
I would like to add that all of these columns are already indexed, and the table contains about 1 million entries.

Now imagine 2 more queries like this one. These 3 queries are fired in parallel. If I comment these queries out,the load average comes down to less than 1.

Is there anything I can do to optimize my database further? MySQL is currently running on 8GB ram, 4 core, CentOS.
You can save some cpu cycles by using the same datatype as the column in your date range. So instead of:

WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')


assuming col3 is a timestamp, use something like:

where col3 &gt;= timestamp('2016-08-01 00:00:00') 
  and col3  &lt; timestamp('2016-08-15 00:00:00')   


You might need to use the unix_timestamp instead of timestamp conversion so that 'between' will work, but you can always do a &gt;= and a &lt; instead of using between.

There's also no need to include Col2 in your select, since col2 will always have the same value in each execution.

So, the query would become:

SELECT COUNT(DISTINCT col1)
FROM conversion_Details c 
WHERE col3 &gt;= timestamp('2016-08-01 00:00:00') 
  AND col3  &lt; timestamp('2016-08-15 00:00:00') 
  AND col4 IN (0, 17) 
  AND col5 = 5 
  AND col2 = 'SOMETHING' ;


A covering index would help to improve efficiency further. Try this one (order matters):

ALTER TABLE conversion_Details
  ADD INDEX covering_index_52431
    (col5, col2, col4, col3, col1) ;