CODEWITHSUNDEEP
mysqlperformancecountinnodb
Orlo
Orlo

Reputation: 828

SELECT COUNT(*) Performance

Lately I discovered that the most consuming requests in my website are the SELECT COUNT(*) a simply request can take sometimes more than a second

SELECT COUNT(*) as count FROM post WHERE category regexp '[[:<:]](17|222)[[:>:]]' AND approve=1 AND date < '2014-01-25 19:08:17';
+-------+
| count |
+-------+
|  3585 |
+-------+
1 row in set (0.49 sec)

I'm not sure what's the problem I've indexes for category, approve and date.

Upvotes: 1

Views: 545

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is your query:

SELECT COUNT(*) as count
FROM post
WHERE category regexp '[[:<:]](17|222)[[:>:]]' AND approve=1 AND
      date < '2014-01-25 19:08:17';

It is not a simple request because the regexp has to run on every row (or every row filtered by the other conditions).

An index on post(approve, date, category) might help. You want one index with the columns listed in that order.

EDIT:

If the values are being stored in a space separated list, you might try this to see if it is faster:

WHERE (concat(' ', category, ' ') like '% 17 %' or concat(' ', category, ' ') like '% 222 %') AND
      approve = 1 AND date < '2014-01-25 19:08:17';

It is possible that these expressions are faster than the regular expression.

And, finally, if you really do need to search for "words" in a field, then consider a full text index. I think you might have to tinker with the options in this case so numbers are allowed in the index.

Upvotes: 3

Related Questions