Suddenly have to rebuild indexes to prevent site from going down

Question

I have been having this problem for a while: https://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column

It seems all is good and when I suddenly get a lot of writes to the table the index seems to die and queries take up to 2 seconds. This always happens when I've got a lot of traffic so the knock on effects pretty much bring my database server down.

If I rebuild the index all is good again. What could be causing these sudden 'breaks' in the index? How can it be prevented?

table def:

+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | int(11) unsigned      | NO   | PRI | NULL    | auto_increment |
| user_id       | mediumint(8) unsigned | YES  | MUL | NULL    |                |
| created       | int(11) unsigned      | YES  | MUL | NULL    |                |
| track         | int(11) unsigned      | YES  | MUL | NULL    |                |
| prop          | int(11) unsigned      | YES  | MUL | NULL    |                |
| sequence      | text                  | YES  |     | NULL    |                |
| location      | varchar(255)          | YES  |     | NULL    |                |
| type          | tinyint(1) unsigned   | YES  | MUL | NULL    |                |
| flagged       | int(11) unsigned      | YES  | MUL | 0       |                |
| status        | tinyint(1) unsigned   | YES  | MUL | 0       |                |
| featured      | tinyint(1) unsigned   | YES  | MUL | 0       |                |
| recommended   | tinyint(1) unsigned   | YES  |     | 0       |                |
| rendered      | tinyint(1) unsigned   | YES  |     | NULL    |                |
| fms_id        | varchar(32)           | YES  | MUL | NULL    |                |
| comments      | text                  | YES  |     | NULL    |                |
| tv            | tinyint(1) unsigned   | YES  |     | 0       |                |
| performers    | varchar(255)          | YES  |     | NULL    |                |
| comp_34_votes | int(11) unsigned      | YES  |     | 0       |                |
| comp_35_votes | int(11)               | YES  |     | 0       |                |
| comp_36_votes | int(11)               | YES  |     | 0       |                |
+---------------+-----------------------+------+-----+---------+----------------+

and the query that suddenly gets slow (if i remove the orderby it's not slow):

SELECT sql_no_cache `p`.`id` as performance_id, `p`.`performers`, `t`.`name` as track_name, `p`.`location`, `p`.`fms_id`
FROM (`performances` p)
JOIN `tracks` t ON `p`.`track` = `t`.`id`
WHERE (p.status = 1 OR (p.status != 2 && p.flagged < 3))
AND `p`.`prop` IN ('1', '2', '3', '4', '5', '6', '8', '10', '11', '13') 
AND `p`.`track` IN ('17', '9', '5', '15', '2', '3', '8', '6', '12', '4', '1') 
AND `p`.`type` IN ('1', '0', '2') 
ORDER BY `p`.`created` desc
LIMIT 11, 12

and the explain:

+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                  | key          | key_len | ref                            | rows | Extra       |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | p     | index  | track,prop,flagged,status,type | created_desc | 5       | NULL                           |   45 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                        | PRIMARY      | 4       | db_123.p.track |    1 | Using where |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+

Answer 1

The problem is not really index fragmentation. It is more associated with the index statistics. If you are using MyISAM, sudden spikes of INSERTs would throw the statistics off in the eyes of the MySQL Query Optimizer. This would cause the MySQL Query Optimizer to take horrific guesses within the EXPLAIN plans of SELECT queries.

If you are using InnoDB, ANALYZE TABLE becomes completely useless.

As long as the table is reasonably small, ANALYZE TABLE is all you can really do for MyISAM. Rebuilding indexes may help InnoDB tables periodically.

As long as tables are not heavily written, index statistics will remain stable and query EXPLAIN plans will remain more consistent.

Just remember: once you have tons of INSERTs, UPDATEs, and DELETEs, all bets are off for trustworthy index statistics until the next rebuild or ANALYZE TABLE.