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'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query:

(
SELECT  `time` 
FROM  `eventlog` 
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time` 
FROM  `screenshots` 
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time` 
FROM  `sitelog` 
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time` 
FROM  `keylog` 
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time` 
FROM  `webcamcaps` 
WHERE userid =  '9819'
)
ORDER BY  `time` ASC 
LIMIT 1;

Here's the EXPLAIN:

+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
| id | select_type  | table            | type | possible_keys | key    | key_len | ref   | rows | Extra          |
+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
|  1 | PRIMARY      | eventlog         | ref  | userid        | userid | 4       | const |  308 |                |
|  2 | UNION        | screenshots      | ref  | userid        | userid | 4       | const | 7850 |                |
|  3 | UNION        | sitelog          | ref  | userid        | userid | 4       | const | 1949 |                |
|  4 | UNION        | keylog           | ref  | userid        | userid | 4       | const | 3589 |                |
|  5 | UNION        | webcamcaps       | ref  | userid        | userid | 4       | const | 8035 |                |
| NULL | UNION RESULT | <union1,2,3,4,5> | ALL  | NULL          | NULL   | NULL    | NULL  | NULL | Using filesort |
+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
6 rows in set (0.00 sec)

How can I optimize this so I'm not looking through so many rows?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Take advantage of the index for each table doing something like this:

(SELECT  MIN(`time`) `time` FROM  `eventlog` WHERE userid =  '9819') UNION ALL
(SELECT  MIN(`time`) FROM  `screenshots` WHERE userid =  '9819')     UNION ALL
(SELECT  MIN(`time`) FROM  `sitelog` WHERE userid =  '9819')         UNION ALL
(SELECT  MIN(`time`) FROM  `keylog` WHERE userid =  '9819')          UNION ALL
(SELECT  MIN(`time`) FROM  `webcamcaps` WHERE userid =  '9819')
ORDER BY  `time` ASC 
LIMIT 1;

or

(SELECT  `time` FROM  `eventlog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `screenshots` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `sitelog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `keylog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `webcamcaps` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
ORDER BY  `time  ASC 
LIMIT 1;

The main idea is to get each subquery to return 1 row.

Give it a Try !!!

share|improve this answer
    
Your first query doesn't allow sorting: Unknown column 'time' in 'order clause' Your second query still examines the same number of rows. –  Ian Feb 26 at 13:48
    
I fixed your first query like this: (SELECT MIN(time) AS all_time FROM eventlog WHERE userid = '9819') UNION ALL (SELECT MIN(time) AS all_time FROM screenshots WHERE userid = '9819') UNION ALL (SELECT MIN(time) AS all_time FROM sitelog WHERE userid = '9819') UNION ALL (SELECT MIN(time) AS all_time FROM keylog WHERE userid = '9819') UNION ALL (SELECT MIN(time) AS all_time FROM webcamcaps WHERE userid = '9819') ORDER BY all_time ASC LIMIT 1; so I'll go with that one since MIN() appears to be faster than sorting. –  Ian Feb 26 at 14:02

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.