Mysql range check instead of index usage on inner join

Question

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 ?

Answer 1

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 !

Answer 2

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.