2

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?

2

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):

    SELECT  
    `part_number`, 
    `part_manufacturer_clean`, 
    `part_number_clean`, 
    `part_heci`, 
    `part_manufacturer`, 
    `part_description`
    FROM `new_products` AS `a`
    WHERE `part_manufacturer_clean` = 'adc'
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`
    WHERE `manulower` = 'adc' ;

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.

1
  • It quite alright to say "At times, the Optimizer is just plain stupid." +1 for the query change. Jun 8 '12 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy