MySQL Unions/Subselects not utilizing keys from associated tables
- by Brett
I've noticed by doing EXPLAINs that when a MySQL union between two tables is used, mysql creates a temporary table, but the temp table does not use keys, so queries are slowed considerably.
Here is an example:
SELECT * FROM (
SELECT
`part_number`,
`part_manufacturer_clean`,
`part_number_clean`,
`part_heci`,
`part_manufacturer`,
`part_description`
FROM `new_products` AS `a`
UNION
SELECT
`part` as `part_number`,
`manulower` as `part_manufacturer_clean`,
`partdeluxe` as `part_number_clean`,
`heci` as `part_heci`,
`manu` as `part_manufacturer`,
`description` as `part_description`
FROM `warehouse` AS `b`
) AS `c`
WHERE `part_manufacturer_clean` = 'adc'
EXPLAIN yields this:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 17206 Using where
2 DERIVED a ALL (NULL) (NULL) (NULL) (NULL) 17743
3 UNION b ALL (NULL) (NULL) (NULL) (NULL) 5757
(NULL) UNION RESULT <union2,3> ALL (NULL) (NULL) (NULL) (NULL) (NULL)
In this case, part_manufacturer_clean and manulower are keys in both tables. When I don't use the subselects and union, and just use one table, everything works fine. I'm not sure if the issue is with the union or with the subselects. Is there any way to union two tables and still use keys/indexes for performance?
