different explain results for same query

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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

4

A compound index on (UserId, Id) should improve things for you:

ALTER TABLE messagesforuser ADD INDEX mf_ix_userid_id(UserId, Id);

5

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.