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

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 '14 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. – FiZiX Feb 26 '14 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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