# different explain results for same query
**URL:** https://forums.percona.com/t/different-explain-results-for-same-query/1406
**Category:** Other MySQL® Questions
**Created:** [May 11, 2010, 4:44am UTC](https://forums.percona.com/t/different-explain-results-for-same-query/1406 "2010-05-11T04:44:52Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@yogesh77](https://forums.percona.com/u/yogesh77)
#### Post date: [May 11, 2010, 4:44am UTC](https://forums.percona.com/t/different-explain-results-for-same-query/1406/1 "2010-05-11T04:44:52Z")
HI,
i am doing explain for a query and getting different results every time, i run explain 5-6 times continuously and get two different output.
see below:
mysql\> explain SELECT ID, MID, VIEWED, TYPE FROM MUSER WHERE ID \> ? AND VIEWEDBYSIP \<\> 2 AND ATTRIBUTE !=‘DELETED’ and USERID = ? LIMIT 3\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
id: 1
select\_type: SIMPLE
table: MUSER
type: ref
possible\_keys: PRIMARY,muser\_1
key: muser\_1
key\_len: 4
ref: const
rows: 271504
Extra: Using where
in next 10 seconds the same explain command showing different results
mysql\> explain SELECT ID, MID, VIEWED, TYPE FROM MUSER WHERE ID \> ? AND VIEWEDBYSIP \<\> 2 AND ATTRIBUTE !=‘DELETED’ and USERID = ? LIMIT 3\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
id: 1
select\_type: SIMPLE
table: MUSER
type: index\_merge
possible\_keys: PRIMARY,muser\_1
key: muser\_1,PRIMARY
key\_len: 4,4
ref: NULL
rows: 1504
Extra: Using intersect(muser\_1,PRIMARY); Using where
1 row in set (0.03 sec)
the database is indexed on id and userid.
What the problem is, sometimes this query took 2-5 minute and sometimes it took few seconds.
can anybody explain why is this happening?
-Yogesh
---
### Author:  [@xaprb](https://forums.percona.com/u/xaprb)
#### Post date: [May 11, 2010, 8:37am UTC](https://forums.percona.com/t/different-explain-results-for-same-query/1406/2 "2010-05-11T08:37:00Z")
The issue is that InnoDB’s estimated statistics vary (they are only estimates, and there is some randomness) so sometimes the optimizer is choosing one plan, sometimes another. You might have to use STRAIGHT\_JOIN or some other hint to prevent this flapping back and forth.
---
### Author:  [@yogesh77](https://forums.percona.com/u/yogesh77)
#### Post date: [May 11, 2010, 11:34pm UTC](https://forums.percona.com/t/different-explain-results-for-same-query/1406/3 "2010-05-11T23:34:34Z")
i use force index ( 2 indexes ) to minimize row scan
SELECT ID, MID, VIEWED, TYPE FROM MUSER force index (PRIMARY,muser\_1) (WHERE ID \> ? AND VIEWEDBYSIP \<\> 2 AND ATTRIBUTE !=‘DELETED’ and USERID = ? LIMIT 3\G
in this case index selection is selective i.e. sometimes it choose index1 or index2 and sometimes both the indexes, is there any way to force mysql always use both the indexes?
-Yogesh
---
### Author:  [@sterin71](https://forums.percona.com/u/sterin71)
#### Post date: [May 12, 2010, 8:57am UTC](https://forums.percona.com/t/different-explain-results-for-same-query/1406/4 "2010-05-12T08:57:50Z")
A compound index on (UserId, Id) should improve things for you:
ALTER TABLE messagesforuser ADD INDEX mf\_ix\_userid\_id(UserId, Id);
---
### Author:  [@yogesh77](https://forums.percona.com/u/yogesh77)
#### Post date: [May 12, 2010, 11:25am UTC](https://forums.percona.com/t/different-explain-results-for-same-query/1406/5 "2010-05-12T11:25:02Z")
Creating a new index on big table of size 40 GB would take 4-5 hrs and need database downtime.
However, i will check it by creating compound index on other production like database.
thank you for the reply.