[Solved]MySQL index work in one table and not work in another table when using IN I was asked if the index is working or not when we use IN keyword in MySQL like below:

 //suppose col has index and the value is almost unique
 SELECT * FROM yourtable WHERE col IN('xxx','yyy','zzz');


In order to get the accurate result, I do the test myself,I created two tables,and index works on one table but not work on another table,I do not know why.Hope someone helps me understand it.Thanks in advance.

The first table is called system_users :

CREATE TABLE system_users(
  id INT(8) NOT NULL AUTO_INCREMENT,
  NAME CHAR(15),
  gender CHAR(1),
  age INT(2),
  department CHAR(1),
  email VARCHAR(20),
  PRIMARY KEY  (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX name_index ON system_users(NAME);

INSERT  INTO system_users(id,NAME,gender,age,department,email) 
  VALUES 
(1,'euser_izpwhzzcz','1',37,'1','yvmk0oqm0lf@mail.com'),
(2,'muser_czepyfklv','1',20,'1','a9wq08gcllk@mail.com'),
(3,'tuser_mwrluajek','0',20,'1','o8jqqebi0bt@mail.com'),
(4,'duser_jzgpheafx','0',22,'2','hegehgsbfmg@mail.com'),
(5,'auser_iacjkekhg','0',36,'2','46drnrja29i@mail.com'),
(6,'euser_eikhwvwsi','1',31,'2','56amq6nayde@mail.com'),
(7,'muser_lblozikah','1',29,'3','qlbtvpzgrej@mail.com'),
(8,'cuser_xhsuuzzur','0',35,'3','klrr6kurylg@mail.com'),
(9,'vuser_iwmklbyzp','0',22,'3','ngdrlguhzrp@mail.com'),
(10,'buser_yfnffsfoc','0',37,'4','o1jvffj4ahu@mail.com');


Now,when I use EXPLAIN to analysis below query sql,I found the index works

EXPLAIN SELECT * FROM system_users WHERE NAME IN('euser_eikhwvwsi','buser_yfnffsfoc');


As we see the value of type is range,in this scenario index seems works when use IN




The second table is called test:

CREATE TABLE test(
  id INT PRIMARY KEY AUTO_INCREMENT,
  c1 VARCHAR(10),
  c2 VARCHAR(10),
  c3 VARCHAR(10),
  c4 VARCHAR(10),
  c5 VARCHAR(10)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX c_index ON test(c1);

INSERT INTO test(c1,c2,c3,c4,c5) VALUES('a1','a2','a3','a4','a5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('b1','b2','b3','b4','b5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('c1','c2','c3','c4','c5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('d1','d2','d3','d4','d5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('e1','e2','e3','e4','e5');

INSERT INTO test(c1,c2,c3,c4,c5) VALUES('a1','b1','c1','d1','e1');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('b1','a2','b2','b4','b5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('c1','a3','c3','d4','c5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('d1','a4','d3','d4','d5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('e1','a5','b5','e4','e5');


Now when I use EXPLAIN to analysis below sql,the index seem not work since the value of type is all

EXPLAIN SELECT * FROM test WHERE c1 IN('a1','b1');




Both of the two tables has ordinary column and datas,but when use IN,why the type of one is range while the other is all?

PS:The version of MySQL in my side is 5.0.67,I have run OPTIMIZE TABLE for both table and still get the same result.

Below are the result when execute ANALYZE TABLE



Note:I have found it has something to do with the data amount in my version,if I insert hundreds of data,then the explain result are expected
In both cases, you should and will get index utilized in your explain plan.  I am not sure why you are getting different plans, but I have got the same by executing your code as it is.

First plan

And the second plan:


There may some fragmentation/table stats issue.  would you execute OPTIMIZE TABLE or ANALYZE TABLE on your tables to see the correct plan?