Why are these MySQL queries so slow?

Question

I don't understand why the following queries show up in slow query log with query time from 12 to 20 seconds.

UPDATE `wp_postmeta` SET `meta_value` = '35' WHERE `post_id` = 1267 AND `meta_key` = 'views'
UPDATE `wp_postmeta` SET `meta_value` = '32' WHERE `post_id` = 874 AND `meta_key` = 'views'
UPDATE `wp_postmeta` SET `meta_value` = '122' WHERE `post_id` = 18557 AND `meta_key` = 'views'
UPDATE `wp_postmeta` SET `meta_value` = '3078' WHERE `post_id` = 21741 AND `meta_key` = 'views'
UPDATE `wp_postmeta` SET `meta_value` = '2657' WHERE `post_id` = 878 AND `meta_key` = 'views'

They look pretty normal to me and executing 1 of them in phpMyAdmin for testing only takes 0.0056s.
The size of the wp_postmeta table here is 77,996.

I wonder why the above queries are so slow and if there is anything I can do to improve them?

What is the datatype of meta_value, if its integer than treat is as integer. It is always better to treat numbers as numbers, not strings — Pir Abdul, Feb 15 '13 at 09:14
use primary index for the post_id column ! that will speed up — Ranjit Kumar, Feb 15 '13 at 09:22
Hi @RanjitKumar, post_id is not unique here. Another column called `meta_id` has the primary key — ericn, Feb 18 '13 at 04:38

score 8 · Accepted Answer · answered Feb 15 '13 at 09:14

8

Better index to use than what @Sandeep suggested is:

CREATE INDEX <some index name>
ON wp_postmeta (meta_key, post_id);

This index will capture all the WHERE clauses and enable the database engine to quickly go to the right row.

answered Feb 15 '13 at 09:14

Ed Heal

59,252
17
87
127

score 4 · Answer 2 · answered Feb 15 '13 at 09:10

4

Create an index on column "post_id". It will solve your problem.

When data is huge, indexes play a significant role in query optimization.

answered Feb 15 '13 at 09:10

Sandeep

1,154
10
16

Why are these MySQL queries so slow?

2 Answers2