UPDATE TOO SLOW

[danesc]

Why would this update be taking so long to execute?

 

# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0

UPDATE playlist_media SET playlist_position=5939 WHERE playlist_media_id=464323 LIMIT 1;

 

 

MySQL version: 5.0.77-log Source distribution

 

mysql> DESCRIBE playlist_media;

--------------

DESCRIBE playlist_media

--------------

 

+-----------------------+------------+------+-----+---------+----------------+

| Field                | Type      | Null | Key | Default | Extra          |

+-----------------------+------------+------+-----+---------+----------------+

| playlist_media_id    | int(11)    | NO  | PRI | NULL    | auto_increment |

| playlist_id          | int(11)    | NO  | MUL | NULL    |                |

| playlist_position    | int(11)    | NO  |    | NULL    |                |

| media_id              | int(11)    | NO  | MUL | NULL    |                |

| playlist_media_status | tinyint(4) | NO  | MUL | NULL    |                |

+-----------------------+------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

 

 

mysql> SHOW INDEX FROM playlist_media;

--------------

SHOW INDEX FROM playlist_media

--------------

 

+----------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

| Table          | Non_unique | Key_name              | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

| playlist_media |          0 | PRIMARY              |            1 | playlist_media_id    | A        |      520018 |    NULL | NULL  |      | BTREE      |        |

| playlist_media |          1 | playlist_id          |            1 | playlist_id          | A        |        4905 |    NULL | NULL  |      | BTREE      |        |

| playlist_media |          1 | media_id              |            1 | media_id              | A        |      260009 |    NULL | NULL  |      | BTREE      |        |

| playlist_media |          1 | playlist_media_status |            1 | playlist_media_status | A        |          4 |    NULL | NULL  |      | BTREE      |        |

+----------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

4 rows in set (0.01 sec)

 

 

Total records in the table 520,018

 

Thank you so much in advance...

 

 

[danesc]

If I do a SELECT with the same WHERE here is what I get

 

mysql> SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1;

--------------

SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1

--------------

 

+-------------------+-------------+-------------------+----------+-----------------------+

| playlist_media_id | playlist_id | playlist_position | media_id | playlist_media_status |

+-------------------+-------------+-------------------+----------+-----------------------+

|            464323 |        6954 |              5939 |  5722053 |                    0 |

+-------------------+-------------+-------------------+----------+-----------------------+

1 row in set (0.00 sec)

 

 

mysql> EXPLAIN SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1;

--------------

EXPLAIN SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1

--------------

 

+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+

| id | select_type | table          | type  | possible_keys | key    | key_len | ref  | rows | Extra |

+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+

|  1 | SIMPLE      | playlist_media | const | PRIMARY      | PRIMARY | 4      | const |    1 |      |

+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.00 sec)

 

 

Thoughts?? Thanks...

[premiso]

You have a lot of INDEXES on that table. When you do an update, especially on a table with 520,018 where all but 1 key is indexed. It takes time to update the indexes.

 

Simple as that. The select does not actively update the index, so it pulls nice and fast, the update does update the indexes.