Mysql range check instead of using index on inner join

Question

I have a serious problem with MySQL (innoDB) 5.0.

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

Inquiry:

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 lines

ResourcePermission - contains 3098354 lines.

In MBCategory, I have several indexes (columns are ordered as in an index):

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

In ResourcePermission, I have several indexes (columns are ordered as in the index):

 Primary - on some column A (primKey). 

When I look at the query plan, Mysql changes the sequence of the tables and first selects the rows from the ResourcePermission, then joins the MBCategory table (crazy idea) and it takes age. So I added STRAIGHT_JOIN to force the innodb engine to use the 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 is the second materialzie problem: In my opinion, mysql should use index A (primKey) in the join operation, instead it performs a Range on each record (index card: 0x400), and it takes a long time again! The force index does not help; mysql still performs the range checked for each record.

In MBCategory there are only 23 lines that fulfill the criteria, and after combining only 75 lines. How can I get mysql to select the correct index for this operation?

Answer 1

Ok, an elementary problem. I owe myself a beer. The system that I recently set up is not the system that I developed - it was assigned to me by my manager to increase productivity (the original team does not have knowledge on this topic).

A few weeks after the improvement of SQL queries, indexes, the number of sql queries that are executed by the application, I did not check one of the most important things in this case!

TYPES OF COLUMN VARIOUS!

The developer who wrote than some kind of code should get a pretty big TALK.

Thanks for the help!