MySQL performance, internal join, how to avoid using temporary and fileort

Question

I have table 1 and table 2.

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

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

Table 1 contains 1 million records, and Table 2 contains 1,000 records.

I am trying to optimize some query using EXPLAIN, and after many attempts I 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 file + filesort in terms of execution. How can i avoid this?

"EVIL" is located in ORDER BY pb.brand ASC. Ordering for this external field is apparently a bottleneck.

Answer 1

. MySQL ; , .

Answer 2

products_brands. brand_name :

ALTER TABLE products_brands ADD INDEX newIdx (brand_name, id_brand)

"orderedByBrandName" , , :

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

, , .

Answer 3

, , , rhs, NULL, , , .

, pb.id_brand pb.brand. "using index" . , .

, , , , .

Answer 4

, , .

Mysql , ORDER BY GROUP BY , .

Thus, you just need to change the connection order using STRAIGHT_JOIN to bypass the order invented by the 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 the variables max_heap_table_size AND tmp_table_size are set to a number large enough to store the results:

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

- 100 megabytes in this example. They can also be installed in the my.cnf configuration file.