# Highly optimized queries are sometimes taking 20s to perform?

**URL:** <https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067>\
**Category:** Other MySQL® Questions\
**Created:** [January 29, 2009, 2:24am UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067 "2009-01-29T02:24:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![garths](https://avatars.discourse-cdn.com/v4/letter/g/aca169/32.png) [@garths](https://forums.percona.com/u/garths)\
**Post date:** [January 29, 2009, 2:24am UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067/1 "2009-01-29T02:24:08Z")

</div>

Hi all,  
I have a PhP/MySQL app running on Joyent virtual servers that has been humming along nicely for a year, but now is hitting serious problems. Queries that used to run fine are now sometimes taking 10 or 20 seconds to complete (but sometimes \<1 second). Table are MyISAM, and locking is not a big issue. There are usually only a couple users at once. The user\_book table has about 600k rows and the book table has about 300k rows.

Here is output of a slow log (some info anonymized):

# Time: \*\*\*\*

# [User&#64;Host](mailto:User&#64;Host): **[**] @ \*\*\*\*

# Query\_time: 12 Lock\_time: 0 Rows\_sent: 22 Rows\_examined: 44

SELECT id, title, author, count, status, user\_book.ranking, asin, locale, image\_url, user\_book.uid, review.uid as review FROM book LEFT JOIN user\_book ON id=bid LEFT JOIN review ON user\_book.uid=review.uid AND user\_book.bid=review.bid WHERE user\_book.uid=25103032;

and here is an explain of the same query.

±—±------------±----------±-------±--------------±-- ------±--------±-------------------------------------±— --±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------±-------±--------------±-- ------±--------±-------------------------------------±— --±------------+  
| 1 | SIMPLE | user\_book | ref | PRIMARY,bid | PRIMARY | 4 | const | 21 | Using where |  
| 1 | SIMPLE | review | eq\_ref | PRIMARY,bid | PRIMARY | 8 | const,\*\*\*\*.user\_book.bid | 1 | Using index |  
| 1 | SIMPLE | book | eq\_ref | PRIMARY | PRIMARY | 4 | \*\*\*\*.user\_book.bid | 1 | |  
±—±------------±----------±-------±--------------±-- ------±--------±-------------------------------------±— --±------------+

I am at a loss. I am a professional software developer, but I know only the very basics when it comes to databases. I’m guessing there is some configuration problem here, as I see no other reason why this query would take 12 seconds to evaluate.

Any ideas?

Thanks,  
Garth

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [January 29, 2009, 6:41am UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067/2 "2009-01-29T06:41:33Z")

</div>

It could be hardware problem. You could check RAID and hdd status.

---

<div class="post-metadata">

**Author:** ![garths](https://avatars.discourse-cdn.com/v4/letter/g/aca169/32.png) [@garths](https://forums.percona.com/u/garths)\
**Post date:** [January 29, 2009, 4:22pm UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067/3 "2009-01-29T16:22:14Z")

</div>

So is this not the kind of problem that appears due to MySQL configuration? Has anyone else dealt with this kind of performance issue?

I don’t have control over the hardware. I asked my hosting provider, and they said everything is ok.

Thanks,  
Garth

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [January 29, 2009, 5:29pm UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067/4 "2009-01-29T17:29:06Z")

</div>

Does this occurs randomly ? Or there is pattern, like “slow query every 10 minutes” ?

Constant period will mean that something is wrong with hardware, or that there is something running in the background (cron job).

---

<div class="post-metadata">

**Author:** ![garths](https://avatars.discourse-cdn.com/v4/letter/g/aca169/32.png) [@garths](https://forums.percona.com/u/garths)\
**Post date:** [January 29, 2009, 10:52pm UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067/5 "2009-01-29T22:52:09Z")

</div>

The slowness seems to happen only the first time I run the query. Second and third times it is fast.

Sorry I should have mentioned this. I guess it would suggest a caching problem of some kind?

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [January 30, 2009, 1:27am UTC](https://forums.percona.com/t/highly-optimized-queries-are-sometimes-taking-20s-to-perform/1067/6 "2009-01-30T01:27:33Z")

</div>

There are two main reasons why it would be faster after the first time. One is the query cache and the other is that indexes have been loaded into the key buffer.

You can prevent MySQL from using the query cache by adding SQL\_NO\_CACHE to your query like so:

SELECT SQL\_NO\_CACHE some\_column FROM some\_table;

If you run your query several times again without the query cache and it still gets faster after the first run, it means that the index pages you need are in the key buffer and its saving you a trip to disk. If that’s the case check the following:

SHOW VARIABLES LIKE ‘key\_buffer\_size’;  
SHOW TABLE STATUS LIKE ‘some\_table’;  
SHOW STATUS LIKE ‘Key\_reads’;  
SHOW STATUS LIKE ‘Key\_read\_requests’;