MySQL performance, inner join, how to avoid Using temporary and filesort

Question

Hi, I have a table 1 and table 2.

Table 1 PARTNUM - ID_BRAND partnum is the primary key id_brand is "indexed"

Table 2 ID_BRAND - BRAND_NAME id_brand is the primary key brand_name is "indexed"

The table 1 contains 1 million of records and the table 2 contains 1.000 records.

I'm trying to optimize some query using EXPLAIN and after a lot of try I have reached a dead end.

EXPLAIN 
SELECT pm.partnum, pb.brand_name
FROM products_main AS pm 
LEFT JOIN products_brands AS pb ON pm.id_brand=pb.id_brand
ORDER BY pb.brand ASC 
LIMIT 0, 10

The query returns this execution plan:

ID, SELECT_TYPE, TABLE, TYPE, POSSIBLE_KEYS, KEY, KEY_LEN , REF, ROWS, EXTRA
1, SIMPLE, pm, range, PRIMARY, PRIMARY, 1, , 1000000, Using where; Using temporary; Using filesort
1, SIMPLE, pb, ref, PRIMARY, PRIMARY, 4, demo.pm.id_pbrand, 1,

The MySQL query optimizer shows a temporary + filesort in the execution plan. How can I avoid this?

The "EVIL" is in the ORDER BY pb.brand ASC. Ordering by that external field seems to be the bottleneck..

Answer 1

Do you have an index on pm.id_brand ?

Answer 2

First, try changing your index on the products_brands table. Delete the existing one on brand_name, and create a new one:

ALTER TABLE products_brands ADD INDEX newIdx (brand_name, id_brand)

Then, the table will already have a "orderedByBrandName" index with the ids you need for the join, and you can try:

EXPLAIN
SELECT pb.brand_name, pm.partnum
FROM products_brands AS pb 
  LEFT JOIN products_main AS pm ON pb.id_brand = pm.id_brand
LIMIT 0, 10

Note that I also changed the order of the tables in the query, so you start with the small one.

Answer 3

First of all, I question the use of an outer join seeing as the order by is operating on the rhs, and the NULL's injected by the left join are likely to play havoc with it.

Regardless, the simplest approach to speeding up this query would be a covering index on pb.id_brand and pb.brand. This will allow the order by to be evaluated 'using index' with the join condition. The alternative is to find some way to reduce the size of the intermediate result passed to the order-by.

Still, the combination of outer-join, order-by, and limit, leaves me wondering what exactly you are querying for, and if there might not be a better way of expressing the query itself.

Answer 4

Try replacing the join with a subquery. MySQL's optimizer kind of sucks; subqueries often give better performance than joins.