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 been having this problem for a while: http://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 |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
share|improve this question
3  
Problem: Index fragmentation. Solution: Change your indexes and/or rebuild indexes regularly. Please post your table and index definitions, as well as the volume and typical profile of the inserts you are getting. –  Nick Chammas Oct 15 '11 at 22:17
    
I typically get about a thousand inserts into this over a 15 minute period which 'breaks' the index. If if remove and create the 'created_desc' index all is good again. It's just pretty scary to do such a think with a thousand inserts happening... –  bradley Oct 15 '11 at 22:28

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

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.