1

I'm trying to simply 'union all' the rows of 12 tables. All tables combined , have 115 rows. However, if I run the query below I get the following when using 'explain' as well as an entry in the mysql-slow.log when set to 'log-queries-not-using-indexes' :

Rows_sent: 115  Rows_examined: 1008

I'm somewhat mystified how mysql is examining 1008 rows when all it has to do is just join(well, 'union') all the rows together. Any hints or pointers would be much appreciated.

Here's the query:

(SELECT id, var_lng_1, 0 as tbl_col FROM tbl1 )

UNION ALL 

(SELECT id, var_lng_1, 1 as tbl_col FROM tbl2 )

UNION ALL 

(SELECT id, var_lng_1, 2 as tbl_col FROM tbl3 )

UNION ALL 

(SELECT id, var_lng_1, 3 as tbl_col FROM tbl4 )

UNION ALL 

(SELECT id, var_lng_1, 4 as tbl_col FROM tbl5 )

UNION ALL 

(SELECT id, var_lng_1, 5 as tbl_col FROM tbl6 )

UNION ALL 

(SELECT id, var_lng_1, 6 as tbl_col FROM tbl7 )

UNION ALL 

(SELECT id, var_lng_1, 7 as tbl_col FROM tbl8 )

UNION ALL 

(SELECT id, var_lng_1, 8 as tbl_col FROM tbl9 )

UNION ALL 

(SELECT id, var_lng_1, 9 as tbl_col FROM tbl10 )

UNION ALL 

(SELECT id, var_lng_1, 10 as tbl_col FROM tbl11 )

UNION ALL 

(SELECT id, var_lng_1, 11 as tbl_col FROM tbl12 );

Any input much appreciated

PS: (just in case this would make a difference) all id's are primary,tiny_int(3), auto_increment columns. I also tried the same query just with the id's (i.e 'select id from....' but that made no difference :(

Full explain output:

[id] => 1  
[select_type] => PRIMARY  
[table] => tbl1  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 7  
[Extra] =>   

[id] => 2  
[select_type] => UNION  
[table] => tbl2  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 18  
[Extra] =>   

[id] => 3  
[select_type] => UNION  
[table] => tbl3  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 8  
[Extra] =>   

[id] => 4  
[select_type] => UNION  
[table] => tbl4  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 10  
[Extra] =>   

[id] => 5  
[select_type] => UNION  
[table] => tbl5  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 11  
[Extra] =>   

[id] => 6  
[select_type] => UNION  
[table] => tbl6  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 14  
[Extra] =>   

[id] => 7  
[select_type] => UNION  
[table] => tbl7  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 10  
[Extra] =>   

[id] => 8  
[select_type] => UNION  
[table] => tbl8  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 6  
[Extra] =>   

[id] => 9  
[select_type] => UNION  
[table] => tbl9  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 3  
[Extra] =>   

[id] => 10  
[select_type] => UNION  
[table] => tbl10  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 2  
[Extra] =>   

[id] => 11  
[select_type] => UNION  
[table] => tbl11  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 6  
[Extra] =>   

[id] => 12  
[select_type] => UNION  
[table] => tbl12  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 20  
[Extra] =>   


[id] =>   
[select_type] => UNION RESULT  
[table] => <union1,2,3,4,5,6,7,8,9,10,11,12>   
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] =>   
[Extra] =>
olly
  • 61
  • 8

1 Answers1

1

You have no WHERE clause so you're returning all rows, and the use of UNION ALL (as opposed to UNION) prevents checking for duplicates in the different sets. A full table scan of each table is required here to produce the correct results. And you have no ORDER BY clause either. There is nothing about this query that could possibly benefit from an index. Using an index simply wouldn't help.

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • Blimey, that was quick.... I think I understand not using an index etc. I'm just curious, why its examining 1008 rows, if there are only 115 in total ? Shouldnt it just examine 115 ? Also - although there arent any duplicates in the tables - if there were any, I want those too, so UNION ALL seems to be the way to go (?) – olly Dec 10 '10 at 05:13
  • Show us the *complete* output of `EXPLAIN (SELECT id, var_lng_1 ... FROM tbl9)` and I might be able to answer that question. I'm thinking it might have something to do with the way `UNION ALL` is implemented but I'm not sure. – Asaph Dec 10 '10 at 05:18