Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql range check instead of index usage on inner join

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 ?

like image 499
Wojtek Rudziński Avatar asked Nov 06 '12 15:11

Wojtek Rudziński


People also ask

Does join use index MySQL?

MySQL only supports using a single index per join. If you want it to utilize two columns as indices in the join, you should create a single index over those two columns. Note that this isn't as bad as it seems, because an index over (a,b) doubles as an index over just a.

What is index range scan MySQL?

The range access method uses a single index to retrieve a subset of table rows that are contained within one or several index value intervals. It can be used for a single-part or multiple-part index.

Does join use index?

Indexes can help improve the performance of a nested-loop join in several ways. The biggest benefit often comes when you have a clustered index on the joining column in one of the tables. The presence of a clustered index on a join column frequently determines which table SQL Server chooses as the inner table.

What is force index in MySQL?

The FORCE INDEX hint acts like USE INDEX ( index_list ) , with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.


2 Answers

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 !

like image 87
Wojtek Rudziński Avatar answered Oct 26 '22 00:10

Wojtek Rudziński


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.

like image 29
thelr Avatar answered Oct 26 '22 01:10

thelr