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?