Mysql slow query logs queries using indexes

Asked Apr 09 '12 at 08:23

Active Apr 09 '12 at 08:23

Viewed 117 times

I have enabled the slow query log to log queries that take more that 2 secs to run as well as log queries that are not using indexes

I have noticed that some frequent queries that are using indexes continue to appear in the logs

Sample query

SELECT `a`.`orderid`, `a`.`date`, `a`.`status`, `a`.`restname`, `a`.`admin_user`,    `a`.`model_type`, `b`.`name` as cityname
FROM `tk_order` AS a
INNER JOIN `tk_city` AS b ON `a`.`cityid` = `b`.`id`
WHERE `a`.`date` =  '2012-04-09'
AND `a`.`status` =  0
AND `a`.`mode` =  3
ORDER BY `a`.`orderid` desc;

The above query I have added a composite index for (date, status, mode), when I run explain this is the output

1 SIMPLE a ref cityid,date_3 date_3 4 const,const 6 Using where; Using temporary; Using filesort
1   SIMPLE  b index PRIMARY,id id 52 2 Using where; Using index; Using join buffer

the order table has about 100000 records, not sure what am I doing wrong here.

asked Apr 09 '12 at 08:23

user160108

2

What indexes do you have on `tk_order` ? – ypercubeᵀᴹ Apr 09 '12 at 08:40
composite index on (date, status, mode) other indexes are orderid, restid – user160108 Apr 09 '12 at 12:55
This may be because you enabled log_queries_not_using_indexes, see similar post: http://stackoverflow.com/questions/13421266/mysql-slow-query-log-logging-fast-queries – user1342336 May 17 '13 at 20:24

Mysql slow query logs queries using indexes

0 Answers0