# Percona 5.6 InnoDB issue not using indexes correctly \[urgent\]

**URL:** <https://forums.percona.com/t/percona-5-6-innodb-issue-not-using-indexes-correctly-urgent/3081>\
**Category:** Other MySQL® Questions\
**Created:** [November 13, 2013, 12:17pm UTC](https://forums.percona.com/t/percona-5-6-innodb-issue-not-using-indexes-correctly-urgent/3081 "2013-11-13T12:17:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mrboon](https://avatars.discourse-cdn.com/v4/letter/m/a88e57/32.png) [@mrboon](https://forums.percona.com/u/mrboon)\
**Post date:** [November 13, 2013, 12:17pm UTC](https://forums.percona.com/t/percona-5-6-innodb-issue-not-using-indexes-correctly-urgent/3081/1 "2013-11-13T12:17:32Z")

</div>

Hey guys, I just installed Percona 5.6 on my new CentOS 6.4 server. It’s a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good 🙂

My old server is a bit less powerful, and was running MySQL 5.1 still. So this was quite an upgrade. But I’m having some issues with InnoDB, it is not using the indexes correctly on some tables it seems. Where on my old machine the same queries were running fine.

Both servers have the same database. I did a mysqldump on the old machine and imported it onto the new Percona 5.6 server. Indexes stayed the same. Both servers use the same my.cnf config settings.

Table items has indexes on: item\_id, item\_format, item\_private and contains about 40 million rows.  
Table formats has index on: format\_id and contains about 250 rows.

SELECT  
i.item\_name, i.item\_key, i.item\_date, f.format\_long  
FROM  
items i, formats f  
WHERE  
i.item\_format = f.format\_id  
AND  
i.item\_private = 0  
ORDER BY  
i.item\_id DESC LIMIT 8

On my old server this query takes about 0.0003 seconds. On the new server it takes over 100 seconds.

Query with EXPLAIN on OLD server. [TABLE=“class: wysiwyg\_table\_data wysiwyg\_table\_ajax”]  
[TR]  
[/TR]  
[TR=“class: wysiwyg\_table\_odd, bgcolor: #FFFFFF”]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_not\_null wysiwyg\_table\_nowrap, align: right”]1[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_not\_null”]SIMPLE[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]i[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]index[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]item\_format[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]PRIMARY[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]4[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_null, align: right”]NULL[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_nowrap, align: right”]8[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_not\_null”]Using where[/TD]  
[/TR]  
[TR=“class: wysiwyg\_table\_even wysiwyg\_table\_marked, bgcolor: #B6C6D7”]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_not\_null wysiwyg\_table\_nowrap, align: right”]1[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_not\_null”]SIMPLE[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]f[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]eq\_ref[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]PRIMARY[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]PRIMARY[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]4[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit”]dbname.i.item\_format[/TD]  
[TD=“class: wysiwyg\_table\_inline\_edit wysiwyg\_table\_nowrap, align: right”]1[/TD]  
[/TR]  
[/TABLE]

Query with EXPLAIN on NEW [problem] server. [TABLE=“class: wysiwyg\_table\_ajax wysiwyg\_table\_pma\_table”]  
[TR]  
[TD] [/TD]  
[/TR]  
[TR=“class: wysiwyg\_table\_odd”]  
[TD=“class: wysiwyg\_table\_right wysiwyg\_table\_data wysiwyg\_table\_not\_null wysiwyg\_table\_nowrap”]1[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_not\_null”]SIMPLE[/TD]  
[TD=“class: wysiwyg\_table\_data”]f[/TD]  
[TD=“class: wysiwyg\_table\_data”]ALL[/TD]  
[TD=“class: wysiwyg\_table\_data”]PRIMARY[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_null”]NULL[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_null”]NULL[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_null”]NULL[/TD]  
[TD=“class: wysiwyg\_table\_right wysiwyg\_table\_data wysiwyg\_table\_nowrap”]219[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_not\_null”]Using temporary; Using filesort[/TD]  
[/TR]  
[TR=“class: wysiwyg\_table\_even, bgcolor: #DFDFDF”]  
[TD=“class: wysiwyg\_table\_right wysiwyg\_table\_data wysiwyg\_table\_not\_null wysiwyg\_table\_nowrap”]1[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_not\_null”]SIMPLE[/TD]  
[TD=“class: wysiwyg\_table\_data”]i[/TD]  
[TD=“class: wysiwyg\_table\_data”]ref[/TD]  
[TD=“class: wysiwyg\_table\_data”]item\_format[/TD]  
[TD=“class: wysiwyg\_table\_data”]item\_format[/TD]  
[TD=“class: wysiwyg\_table\_data”]4[/TD]  
[TD=“class: wysiwyg\_table\_data”]dbname.f.format\_id[/TD]  
[TD=“class: wysiwyg\_table\_right wysiwyg\_table\_data wysiwyg\_table\_nowrap”]3026[/TD]  
[TD=“class: wysiwyg\_table\_data wysiwyg\_table\_not\_null”]Using where[/TD]  
[/TR]  
[/TABLE]  
You can see that it’s using temporary and filesort. This seems to be the reason for the slowness.

Any idea how I could resolve this issue?

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [November 13, 2013, 2:51pm UTC](https://forums.percona.com/t/percona-5-6-innodb-issue-not-using-indexes-correctly-urgent/3081/2 "2013-11-13T14:51:41Z")

</div>

I suggest to try it with STRAIGHT\_JOIN or with index hint (USE INDEX or FORCE INDEX) to check if it changes the query execution path first.  
From the docs:  
“STRAIGHT\_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.”

```auto

SELECT
i.item_name, i.item_key, i.item_date, f.format_long
FROM
formats f STRAIGHT_JOIN items i
ON f.format_id = i.item_format
WHERE
i.item_private = 0
ORDER BY i.item_id DESC LIMIT 8

```

OR

```auto

SELECT
i.item_name, i.item_key, i.item_date, f.format_long
FROM
items i, formats f USE INDEX(PRIMARY)
WHERE
i.item_format = f.format_id
AND
i.item_private = 0
ORDER BY
i.item_id DESC LIMIT 8

```

As you mentioned the query EXPLAIN output changed between MySQL versions it needs to check for changelog in documentation to verify what exactly changed between the versions. Also did you checked the same query EXPLAIN result on version 5.5 ?