1

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?

1 Answer 1

1

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 !!!

2
  • Your first query doesn't allow sorting: Unknown column 'time' in 'order clause' Your second query still examines the same number of rows.
    – FiZiX
    Feb 26, 2014 at 13:48
  • 1
    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.
    – FiZiX
    Feb 26, 2014 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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