Improve query that compares from several columns

[king.oslo]

Hello,

 

I have made this query:

 

SELECT DISTINCT tmp.id FROM 
(
SELECT id FROM database.table_index WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_truser WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_undies WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_about_product WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_cart_2 WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_dirty WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_faq WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_final WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_gtbill_postpay WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_lek WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_pool_product WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_custompay WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_send WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_send_bank WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_send_order WHERE unixTimestamp > 1266793200 
UNION 
SELECT id FROM database.table_tnc WHERE unixTimestamp > 1266793200

) AS tmp WHERE id IN (
SELECT id FROM database.table_index WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_truser WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_undies WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_about_product WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_cart_2 WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_dirty WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_faq WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_final WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_gtbill_postpay WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_lek WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_pool_product WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_custompay WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_send WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_send_bank WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_send_order WHERE unixTimestamp < 1266793200 
UNION 
SELECT id FROM database.table_tnc WHERE unixTimestamp < 1266793200
)

 

 

Each table has identical structure:

 

Field         	Type
id	        int(10)
unixTimestamp	int(20)

 

THE QUESTION:

----------------------

When it executes, it is very slow (14 sec), returning about 50 rows. I figured there must be a better way to make this query that is faster and better?

 

Thanks,

Marius

[fenway]

Assuming you have an index on your timestamp column, you're at the limit.