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?
Best Answer
The optimizer is not that clever - yet (see footer).
You could still use
UNION
, if you rewrite the query (a job that could/should be done by a decent optimizer):You can also try latest MariaDB versions (5.3 and 5.5) that have several improvements on the optimizer (if changing to MariaDB is an option).
You can also try MySQL 5.6 (still in beta) which has some improvements on the optimizer, too.