MySQL optimization for SELECT FROM multiple tables with quite some AND operations?

This query takes about 5 - 10 seconds to run which is unacceptable for rendering a web page:

SELECT part . * , brand . * 
FROM  `part` ,  `model2year2part` ,  `brand2subcategory2part` ,  `brand2subcategory` ,  `brand` 
WHERE  `model2year2part`.`model2year_id` =  '9521'
AND  `model2year2part`.`part_id` =  `part`.`id` 
AND  `brand2subcategory`.`subcategory_id` =  '1'
AND  `brand2subcategory2part`.`brand2subcategory_id` =  `brand2subcategory`.`id` 
AND  `brand2subcategory2part`.`part_id` =  `part`.`id` 
AND  `brand`.`id` =  `part`.`brand_id` 

ANSIfied:

SELECT p.*, 
       b.* 
  FROM PART p
  JOIN brand b ON b.id = p.brand_id
  JOIN model2year2part m ON m.part_id = p.id
  JOIN brand2subcategory2part b2p ON b2p.part_id = p.id 
  JOIN brand2subcategory b2 ON b2.id = b2.brand2subcategory_id
 WHERE m.model2year_id = '9521'
   AND b2.subcategory_id = '1'

No matter what you feed to model2year2part.model2year_id and brand2subcategory.subcategory_id as input.

EXPLAIN results of the query: http://i.stack.imgur.com/aYtXl.jpg

I have done all possible indexing / unique indexing for all the 5 tables.

• part: http://i.stack.imgur.com/9z2Mm.jpg
• model2year开发者_如何学JAVA2part: http://i.stack.imgur.com/jeLMY.jpg
• brand2subcategory2part: http://i.stack.imgur.com/ZkI5n.jpg
• brand2subcategory: http://i.stack.imgur.com/fbygX.jpg
• brand: http://i.stack.imgur.com/pfO33.jpg

The number of total records in each table:

• part: 728,534
• model2year2part: 15,012,595 (is this the culprit?)
• brand2subcategory2part: 729,030
• brand2subcategory: 8,111
• brand: 875

What may be the culprit? Is there any way to optimize this query other than a hardware upgrade?

First, I noticed what looks like a mistake in your fourth Join clause:

ON b2.id = b2.brand2subcategory_id

I'm assuming this should be:

ON b2.id = b2p.brand2subcategory_id

Second, you might try breaking out the filtering joins from the ones needed for the output. That allows you to do a Explain on just the subquery by itself to see what might be causing the issue:

Select P.*, B.*
From Part As P
    Join Brand As B
        On B.Id = P.brand_Id
Where P.part_id In  (
                        Select M1.part_Id
                        From mode2year2part As M1
                                Join brand2subcategory2part As B2P
                                    On B2P.part_id = M1.part_id
                                Join brand2subcategory As B2
                                    On B2.Id = B2P.brand2subcategory_id
                        Where m1.model2year_id = '9521'
                            And B2.subcategory_id = '1'
                        )