# MySQL query sometimes runs slow, sometimes fast

**URL:** <https://forums.percona.com/t/mysql-query-sometimes-runs-slow-sometimes-fast/4769>\
**Category:** General Questions\
**Created:** [March 11, 2016, 2:46am UTC](https://forums.percona.com/t/mysql-query-sometimes-runs-slow-sometimes-fast/4769 "2016-03-11T02:46:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![zangetsKid](https://avatars.discourse-cdn.com/v4/letter/z/54ee81/32.png) [@zangetsKid](https://forums.percona.com/u/zangetsKid)\
**Post date:** [March 11, 2016, 2:46am UTC](https://forums.percona.com/t/mysql-query-sometimes-runs-slow-sometimes-fast/4769/1 "2016-03-11T02:46:19Z")

</div>

Hi,  
I am having problem with this simple MySQL query:

```auto
select sender as id from message where status=1 and recipient=1 

```

where sender table has multi millions of rows.

When I run this on SequelPro, it runs really slow for the first time, ~4 seconds or more, and the next execution it run really fast, ~0.018 seconds. However, if I run again after couple of minutes, it will do the same thing again.  
I tried to use SQL\_NO\_CACHE, and it still gives me the same result.  
The DB engine is innoDB, and the DB is MySQL Percona XtraDB cluster. Here is the explain results:

```auto

			+--+-----------+-------+----+----------------------+----+-------+---------------+-------+-----+
			|id|select_type|table |type|possible_keys |key |key_len|ref |row |Extra|
			+--+-----------+-------+----+----------------------+----+-------+---------------+-------+-----+
			| 1|SIMPLE |message|ref |recipient,status, sent|sent|12 |const,const |2989 |NULL |
			+--+-----------+-------+----+----------------------+----+-------+---------------+-------+-----+
			

```

“sent” is an index of multi-column of (recipient, status). Does anyone has any idea to fix this problem?

Thank you.

---

<div class="post-metadata">

**Author:** ![vadimtk](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadimtk/32/9528_2.png) [@vadimtk](https://forums.percona.com/u/vadimtk)\
**Post date:** [March 11, 2016, 6:46pm UTC](https://forums.percona.com/t/mysql-query-sometimes-runs-slow-sometimes-fast/4769/2 "2016-03-11T18:46:45Z")

</div>

Can you post “SHOW CREATE TABLE message” ?

---

<div class="post-metadata">

**Author:** ![zangetsKid](https://avatars.discourse-cdn.com/v4/letter/z/54ee81/32.png) [@zangetsKid](https://forums.percona.com/u/zangetsKid)\
**Post date:** [March 13, 2016, 8:50pm UTC](https://forums.percona.com/t/mysql-query-sometimes-runs-slow-sometimes-fast/4769/3 "2016-03-13T20:50:38Z")

</div>

Here is the “SHOW CREATE TABLE message”

```auto
CREATE TABLE `message` ( `id` int(20) NOT NULL AUTO_INCREMENT, `sender` bigint(20) NOT NULL, `recipient` bigint(20) NOT NULL, `status` int(5) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `id` (`id`), KEY `recipient` (`recipient`), KEY `sender` (`sender`), KEY `date` (`date`), KEY `status` (`status`), KEY `sent` (`status`,`recipient`) ) ENGINE=InnoDB AUTO_INCREMENT=90224500 DEFAULT CHARSET=latin1; 

```

Any idea what’s wrong?

---

<div class="post-metadata">

**Author:** ![MrAwanish](https://avatars.discourse-cdn.com/v4/letter/m/c68b51/32.png) [@MrAwanish](https://forums.percona.com/u/MrAwanish)\
**Post date:** [July 8, 2016, 12:03am UTC](https://forums.percona.com/t/mysql-query-sometimes-runs-slow-sometimes-fast/4769/4 "2016-07-08T00:03:57Z")

</div>

Try using MySql Table Partitioning features.