16

I'm having a serious problem with MySQL (innoDB) 5.0.

A very simple SQL query is executed with a very unexpected query plan.

The query:

SELECT 
SQL_NO_CACHE
mbCategory.*

FROM 
MBCategory mbCategory 

INNER JOIN ResourcePermission as rp
    ON rp.primKey = mbCategory.categoryId 

where mbCategory.groupId = 12345 AND mbCategory.parentCategoryId = 0 
limit 20;

MBCategory - contains 216583 rows

ResourcePermission - contains 3098354 rows.

In MBCategory I've multiple indexes (columns order as in index):

Primary (categoryId)
A (groupId,parentCategoryId,categoryId)
B (groupId,parentCategoryId)

In ResourcePermission I've multiple indexes (columns order as in index):

Primary - on some column
A (primKey).

When I look into query plan Mysql changes tables sequence and selects rows from ResourcePermission at first and then it joins the MBCategory table (crazy idea) and it takes ages. So I added STRAIGHT_JOIN to force the innodb engine to use correct table sequence:

SELECT

STRAIGHT_JOIN SQL_NO_CACHE
mbCategory.*

FROM 
MBCategory
 mbCategory 

INNER JOIN ResourcePermission as rp
    ON rp.primKey = mbCategory.categoryId 

where mbCategory.groupId = 12345 AND mbCategory.parentCategoryId = 0 
limit 20;

But here the second problem materialzie: In my opinion mysql should use index A (primKey) on the join operation instead it performs Range checked for each record (index map: 0x400) and it again takes ages ! Force index doesn't help, mysql still performing Range checked for each record .

There are only 23 rows in the MBCategory which fulfill where criteria, and after join there are only 75 rows. How can I make mysql to choose correct index on this operation ?

Sam
  • 7,252
  • 16
  • 46
  • 65
Wojtek Rudziński
  • 689
  • 1
  • 5
  • 8
  • I think MySQL cannot directly determine how many rows it must read to find 20 matching rows; it therefore chooses the larger table for 20 matches then perform the inner join. What happens if you remove the limit? – Salman A Nov 06 '12 at 15:47
  • Removing the limit changes nothing. I simply need mysql to use the correct index not the range check – Wojtek Rudziński Nov 07 '12 at 07:30

2 Answers2

50

Ok, elementary problem. I owe myself a beer. The system I'm recently tunning is not a system I've developted - I've been assigned to it by my management to improve performance (originall team doesn't have knowledge on this topic).

After fee weeks of improving SQL queries, indexes, number of sql queries that are beeing executed by application I didn't check one of the most important things in this case !!

COLUMN TYPES ARE DIFFERENT !

Developer who have written than kind of code should get quite a big TALK.

Thanks for help !

Wojtek Rudziński
  • 689
  • 1
  • 5
  • 8
  • 1
    It's worth pointing out that MySQL 5.7 improves this situation, where a 'dynamic range' will be supported. Please see this blog post: http://mysqlserverteam.com/dynamic-range-access-and-recent-changes/ (which links to here.) – Morgan Tocker Apr 22 '14 at 16:33
  • 2
    I also owe you a beer – Cagatay Gurturk Oct 26 '14 at 11:00
  • 1
    Well, I'll be - I owe you a beer too. – Leonardo Herrera Mar 05 '15 at 21:28
  • 1
    It is already too late, and you would have had enough beer already by now, but Please accept a beer from me too :) This solved my issue too, was really puzzled. Thanks a ton. – shanti Apr 12 '18 at 12:51
  • Thanks, @Morgan Tocker. The columns types all lined up in my case, but queries which are doing a range-check were taking an hour to run. Upgraded from 5.6 to 5.7 and the same queries are now running an order of magnitude faster. – jdhildeb Mar 30 '20 at 14:52
  • i had same problem COLUMN TYPES ARE DIFFERENT – Ivan May 27 '20 at 07:15
3

I had the same problem with a different cause. I was joining a large table, and the ON clause used OR to compare the primary key (ii.itemid) to two different columns:

SELECT *
FROM share_detail sd
JOIN box_view bv ON sd.container_id = bv.id
JOIN boxes b ON b.id = bv.shared_id
JOIN item_index ii ON ii.itemid = bv.shared_id OR b.parent_itemid = ii.itemid;

Fortunately, it turned out the parent_itemid comparison was redundant, so I was able to remove it. Now the index is being used as expected. Otherwise, I was going to try splitting the item_index join into two separate joins.

thelr
  • 1,134
  • 11
  • 30