# Index usage with big IN() **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 13, 2012, 9:50am UTC](https://forums.percona.com/t/index-usage-with-big-in/1846 "2012-07-13T09:50:36Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![vaceletm](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vaceletm/32/884_2.png) [@vaceletm](https://forums.percona.com/u/vaceletm)\ **Post date:** [July 13, 2012, 9:50am UTC](https://forums.percona.com/t/index-usage-with-big-in/1846/1 "2012-07-13T09:50:36Z")
Hi there, I have some troubles understanding what happens with one of my queries. Here is the table structures. There is a tiny dataset within (less than 5000 entries in each). CREATE TABLE `tracker_artifact` ( `id` int(11) NOT NULL auto\_increment, `tracker_id` int(11) NOT NULL, `last_changeset_id` int(11) NOT NULL, `submitted_by` int(11) NOT NULL, `submitted_on` int(11) NOT NULL, `use_artifact_permissions` tinyint(1) NOT NULL default ‘0’, PRIMARY KEY (`id`), KEY `idx_tracker_id` (`tracker_id`), KEY `idx_my` (`submitted_by`,`tracker_id`,`last_changeset_id`), KEY `idx_last_changeset_id` (`last_changeset_id`,`id`)) ENGINE=MyISAM AUTO\_INCREMENT=2842 DEFAULT CHARSET=utf8;CREATE TABLE `tracker_changeset` ( `id` int(11) NOT NULL auto\_increment, `artifact_id` int(11) NOT NULL, `submitted_by` int(11) default NULL, `submitted_on` int(11) NOT NULL, `email` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `artifact_id` (`artifact_id`,`id`)) ENGINE=MyISAM AUTO\_INCREMENT=3342 DEFAULT CHARSET=utf8; Then I have a query that looks like that: SELECT c.idFROM tracker\_changeset AS c INNER JOIN tracker\_artifact AS a ON (a.id = c.artifact\_id)WHERE a.id IN (928,933,934,…) AND c.id IN (1398,1432,…); But the 2 ranges are quite big (~1900 values in each) and the query is taking a lot of time (8s) but I don’t undestand why. Even explain is taking ages: ±—±------------±------±------±--------------------±------------±--------±-------------±-----±-------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±------±------±--------------------±------------±--------±-------------±-----±-------------------------+| 1 | SIMPLE | a | range | PRIMARY | PRIMARY | 4 | NULL | 1909 | Using where; Using index | | 1 | SIMPLE | c | ref | PRIMARY,artifact\_id | artifact\_id | 4 | a.id | 1 | Using where; Using index | ±—±------------±------±------±--------------------±------------±--------±-------------±-----±-------------------------+2 rows in set (3.33 sec) 2 questions arise: → What I’m doing wrong there → What is the impact of huge IN() statements? ---
**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\ **Post date:** [July 17, 2012, 1:37am UTC](https://forums.percona.com/t/index-usage-with-big-in/1846/2 "2012-07-17T01:37:39Z")
Hi, Query plan looks perfect but it’s kinda estimation so we need to know what exactly the query is doing. Please provide output of below commands and some more information like MySQL version, my.cnf parameters etc. FLUSH STATUS; SELECT c.id FROM tracker\_changeset AS c… SHOW STATUS; Profiling the query can also be helpful. SET PROFILING=1; SELECT c.id FROM tracker\_changeset AS c… SHOW PROFILE FOR query 1; ---
**Author:** ![vaceletm](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vaceletm/32/884_2.png) [@vaceletm](https://forums.percona.com/u/vaceletm)\ **Post date:** [July 17, 2012, 8:50am UTC](https://forums.percona.com/t/index-usage-with-big-in/1846/3 "2012-07-17T08:50:17Z")
Hi Niljoshi, thanks for your answer here is the status (profiling not available on my old version of mysql) mysql\> SHOW STATUS like ‘handler\_read%’;±----------------------±------+| Variable\_name | Value |±----------------------±------+| Handler\_read\_first | 0 || Handler\_read\_key | 22435 || Handler\_read\_next | 14418 || Handler\_read\_prev | 0 || Handler\_read\_rnd | 0 || Handler\_read\_rnd\_next | 0 |±----------------------±------+6 rows in set (0.00 sec) BTW, in the meantime I ran an “optimize” on all tables involved and it basically solved the perf issue. Thus, I had to modify one of the index on tracker\_changeset table (artifact\_idx) that where on (artifact\_id, id), id being the PK. Now the index is only on ‘artifact\_id’ and the query is fast. I’m a bit confused because: - I through it was a good practice to add the PK as second index member - If I convert my tables to InnoDB, the query is fast.