CODEWITHSUNDEEP
mysqlsqlquery-optimization
Satish Patel
Satish Patel

Reputation: 11

Limit With order by takes long time in mysql for only 10 records

Here is my query which takes more than 5 second s to fetch 10 records n time gets bigger by changing the offsets in limit clause. Table contain 12 million records.

SELECT device_id
    ,media_id
    ,limit1.play_date
    ,limit1.start_time
    ,limit1.end_time
    ,SUBTIME(limit1.end_time, limit1.start_time) AS playback_duration
FROM device_media_log
INNER JOIN (
    SELECT play_date
        ,start_time
        ,end_time
        ,device_media_id
    FROM device_media_log
    ORDER BY play_date DESC
        ,start_time DESC
        ,end_time DESC limit 0
        ,10
    ) AS limit1 ON device_media_log.device_media_id = limit1.device_media_id;

explain plan::

+----+-------------+------------------+--------+---------------+---------+---------+------------------------+---------+----------------+
| id | select_type | table            | type   | possible_keys | key     | key_len | ref                    | rows    | Extra          |
+----+-------------+------------------+--------+---------------+---------+---------+------------------------+---------+----------------+
|  1 | PRIMARY     | <derived2>       | ALL    | NULL          | NULL    | NULL    | NULL                   |      10 |                |
|  1 | PRIMARY     | device_media_log | eq_ref | PRIMARY       | PRIMARY | 8       | limit1.device_media_id |       1 |                |
|  2 | DERIVED     | device_media_log | ALL    | NULL          | NULL    | NULL    | NULL                   | 8345645 | Using filesort |
+----+-------------+------------------+--------+---------------+---------+---------+------------------------+---------+----------------+

here is create table::

CREATE TABLE `device_media_log` (
  `device_media_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL DEFAULT '0',
  `media_id` bigint(20) NOT NULL DEFAULT '0',
  `playback_type_id` tinyint(4) NOT NULL DEFAULT '0',
  `playback_id` int(11) NOT NULL DEFAULT '0',
  `play_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `client_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`device_media_id`),
  KEY `Index_media_id` (`media_id`),
  KEY `Index_device_id` (`device_id`),
  KEY `Index_play_date` (`play_date`),
  KEY `Index_start_time` (`start_time`),
  KEY `Index_end_time` (`end_time`),
  KEY `Index_client_id` (`client_id`)
) 
ENGINE=InnoDB AUTO_INCREMENT=8366229 DEFAULT CHARSET=latin1

Describe after adding compound index -+-------+---------------+----------------- +---------+------+------+-------+ | 1 | SIMPLE | device_media_log | index | NULL | index_composite | 12 | NULL | 10 | |

Upvotes: 0

Views: 1234

Answers (1)

Meherzad
Meherzad

Reputation: 8553

Try this query 

SELECT device_id
   ,media_id
   ,limit1.play_date
   ,limit1.start_time
   ,limit1.end_time
   ,SUBTIME(limit1.end_time, limit1.start_time) AS playback_duration
FROM 
   device_media_log
ORDER BY 
    play_date DESC
    ,start_time DESC
    ,end_time DESC 
limit 0, 10;

There is no need of subquery as you are directly using the result. Also the explain statement shows that none of your created index is used.

Create a compound index on following column play_date, start_time, end_time.

ALTER TABLE device_media_log ADD INDEX device_media_log_date_time(play_date, start_time, end_time);

hope this helps...

Upvotes: 1

Related Questions