[Spce-user] DBCleaner - not using index.

[Walter Klomp]

Hi,

I just saw DBCleaner in slow queries taking a lot of time as MySQL is not using the index because of the from_unixtime instead of the actual unixtime…  (40 seconds or more)

mysql> explain select id from cdr where from_unixtime(start_time) >= '2016-01-01 00:00:00' and from_unixtime(start_time) < date_add('2016-01-01 00:00:00', interval 1 month)  limit 10000;
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | cdr   | index | NULL          | stime_idx | 7       | NULL | 6064490 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+—————————————+

it’s better to convert the 2016-01-01 00:00:00 to the unixtime and then put that value in the where… that will make MySQL use the index.

Thanks
Walter.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/mailman/private/spce-user_lists.sipwise.com/attachments/20160613/a0550c86/attachment.html>