Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
share|improve this question
    
+1 for actually researching and find your own answer !!! –  RolandoMySQLDBA Nov 30 '12 at 0:04

1 Answer 1

up vote 1 down vote accepted

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 | 
share|improve this answer
    
+1 for actually researching and find your own answer !!! –  RolandoMySQLDBA Nov 30 '12 at 0:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.