Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jun 8 '12 at 7:19

This question came from our site for professional system and network administrators.

1 Answer 1

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.

share|improve this answer
    
It quite alright to say "At times, the Optimizer is just plain stupid." +1 for the query change. –  RolandoMySQLDBA Jun 8 '12 at 15:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.