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 |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+