Mysql – How to optimize this query

MySQLoptimizationperformancequery-performance

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?

Best Answer

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;

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

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

You misunderstand what the 'rows' field is. It is the number of rows that mysql estimates it shall need to read to satisfy your query. This value can be quite inaccurate. It does not mean this is the number of rows in the result - or the actual number of rows read by mysql

Mysql – Filesort while using primary key for orderby

KEY `index_dedupe_variables`
(`deleted_at`,`state`,`reference_type`,`reference`,`id`,`client_identifier`)

Try removing that index and replacing it with this one:

KEY `new_index`
(`deleted_at`,`state`,`reference_type`,`id`)

Even when an index is being used, only the useful portion of the index is used; columns to the right of the first instance where the index cannot be used are not considered in that index.

In your query, 'reference' uses a wildcard on the left side, making that column of the index not useful, and eliminating the use of `id` in the index.

Your explain plan tells you this is happening:

ref: const,const,const

From this, you can tell that only the first three columns of the index are actually being used by the query.

By redefining the index to eliminate the unusable "reference" column, we can use all 4 of the columns of the index, and since the index is already sorted in the order you want, we can then do this:

WHERE `deleted_at` IS NULL
  AND state = 'Maharashtra'
  AND reference_type = 4 
  AND reference LIKE '%8858' 
ORDER BY deleted_at, state, reference_type, id

We don't really want them ordered by those first three columns, but since all they are all equality comparisons, ordering by them won't change the results and it should be "obvious" to the optimizer that it can simply return the results in index order... no filesort needed.

Your EXPLAIN should change to "Using Where" and possibly "Using index" (since this can mean "using index for lookups" even when it's not a covering index.)

It's possible that the optimizer might catch on and not use a filesort even if your ORDER BY were only `id`\ so you might try it both ways after you change the index.

Also, remove the use index from your query. It shouldn't be needed.

Best Answer

Give it a Try !!!

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

Mysql – Filesort while using primary key for orderby

Related Question