MySQL server taking multiple seconds to run COUNT(*) query using an index on a MyISAM table

Question

I have a MySQL database on a shared webhost, which has been running with its application for a couple years.

Today, this simple query:

SELECT COUNT(*) FROM tickets WHERE id = 123

Took 16 seconds to run. There is an index on id, the table is MyISAM, and this query usually takes milliseconds. The table has about 1 million rows, and the query should return a COUNT of about 250,000.

SHOW PROCESSLIST shows that all the time is being spent Sending data. I don't have root access, so no profiling help.

I have the same database on another server from the same host, and the query executes in 0.06 seconds.

The host thinks everything is running normally on the server. Is there anything I can point them at?

mysql> EXPLAIN SELECT COUNT(*) FROM tickets WHERE id = 214; 
| 1 | SIMPLE | tickets | ref | id | id | 2 | const | 215307 | Using index |

• RolandoMySQLDBA: +1 for actually researching and find your own answer !!!

Answer 1

The problem appears to have been that the cardinality of my indices was NULL. I am unsure why the indices would revert to this state when they were apparently fine yesterday.

I discovered this problem by running SHOW INDEX FROM tickets.

I ran ANALYZE TABLE tickets. Now the EXPLAIN looks subtly different-- the rows have dropped to 1:

mysql> explain select count(*) from tickets WHERE id = 214;
| 1 | SIMPLE | tickets | const | PRIMARY | PRIMARY | 2 | const | 1 | Using index | 

• RolandoMySQLDBA: +1 for actually researching and find your own answer !!!