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

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, SI开发者_如何学运维MPLE, 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..

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.

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

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.

This question is somewhat outdated, but I did find it, and so will other people.

Mysql uses temporary if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

So you just need to have the join order reversed by using STRAIGHT_JOIN, to bypass the order invented by optimizer:

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

Also make sure that max_heap_table_size AND tmp_table_size variables are set to a number big enough to store the results:

SET global tmp_table_size=100000000;
SET global max_heap_table_size=100000000;

-- 100 megabytes in this example. These can be set in my.cnf config file, too.