Index missing?

Support & Bugs
1

Hi!

Please note that I don’t have a real problem, everything works fine. I just turned on “log-queries-not-using-indexes” in mysql today and noticed that the following query is filling the logfile quite fast. (I snipped some columns for readability.) The queries are really fast, so there are no performance problems either, I am just curious: Is there an index missing or is it maybe just the order by clause?


( SELECT [snipped columns], 0 as priority
                                        FROM piwik_log_visit
                                        WHERE visit_last_action_time >= '2012-12-20 18:35:40' AND idsite = '1'  AND config_id = '^OÄ^B»f¡Ê<8e>'
                                        ORDER BY visit_last_action_time DESC
                                        LIMIT 1
                         )
                                        UNION
( SELECT  [snipped columns], 1 as priority
                                        FROM piwik_log_visit
                                        WHERE visit_last_action_time >= '2012-12-20 18:35:40' AND idsite = '1'  AND idvisitor = '^<88>gg|ºò½'
                                        LIMIT 1
                         )
                                        ORDER BY priority DESC
                                        LIMIT 1;
2

I would prepend EXPLAIN to each sub query (before and after UNION) and post the result here for each?

3

Here’s the output of EXPLAIN:


+----+--------------+-----------------+-------+---------------------------------------------------------------------------+------------------------------+---------+-------------+------+----------------+
| id | select_type  | table           | type  | possible_keys                                                             | key                          | key_len | ref         | rows | Extra          |
+----+--------------+-----------------+-------+---------------------------------------------------------------------------+------------------------------+---------+-------------+------+----------------+
|  1 | PRIMARY      | piwik_log_visit | range | index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor | index_idsite_config_datetime | 20      | NULL        |    1 | Using where    |
|  2 | UNION        | piwik_log_visit | ref   | index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor | index_idsite_idvisitor       | 12      | const,const |    1 | Using where    |
| NULL | UNION RESULT | <union1,2>      | ALL   | NULL                                                                      | NULL                         | NULL    | NULL        | NULL | Using filesort |
+----+--------------+-----------------+-------+---------------------------------------------------------------------------+------------------------------+---------+-------------+------+----------------+

It seems, for the first select “index_idsite_config_datetime” is taken, for the second “index_idsite_idvisitor”. I am not really a db expert, so I can’t say if this good or not.