# ORDER by datetime - still using filesort :(

**URL:** <https://forums.percona.com/t/order-by-datetime-still-using-filesort/1219>\
**Category:** Other MySQL® Questions\
**Created:** [July 25, 2009, 8:06pm UTC](https://forums.percona.com/t/order-by-datetime-still-using-filesort/1219 "2009-07-25T20:06:46Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nerolabs](https://avatars.discourse-cdn.com/v4/letter/n/cc9497/32.png) [@nerolabs](https://forums.percona.com/u/nerolabs)\
**Post date:** [July 25, 2009, 8:06pm UTC](https://forums.percona.com/t/order-by-datetime-still-using-filesort/1219/1 "2009-07-25T20:06:46Z")

</div>

I am working on a tough mySQL problem, and need some help optimizing an ORDER BY query. Here is a table description:

CREATE TABLE `urls` (  
`urls_id` bigint(32) NOT NULL auto\_increment,  
`urls_url` varchar(96) collate utf8\_unicode\_ci NOT NULL default ‘’,  
`urls_last_updated` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`urls_added` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`urls_active` tinyint(1) NOT NULL default ‘0’,  
`urls_type` enum(‘MAIN’,‘CATEGORY’,‘LISTING’) collate utf8\_unicode\_ci NOT NULL default ‘LISTING’,  
`urls_name` varchar(255) collate utf8\_unicode\_ci NOT NULL default ‘’,  
`urls_update_count` int(16) NOT NULL default ‘0’,  
`urls_hold_until` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`urls_last_discovered` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`urls_dropdown_name` varchar(64) collate utf8\_unicode\_ci NOT NULL default ‘’,  
`urls_flag` enum(‘NONE’,‘EXPIRED’,‘FLAGGED’,‘REMOVED’) collate utf8\_unicode\_ci NOT NULL default ‘NONE’,  
PRIMARY KEY (`urls_id`),  
UNIQUE KEY `urls_unique` (`urls_url`),  
KEY `get_urls` (`urls_active`,`urls_hold_until`,`urls_flag`,`urls_type`),  
KEY `last_updated` (`urls_last_updated`),  
KEY `get_urls2` (`urls_active`,`urls_hold_until`,`urls_flag`,`urls_type`,`ur ls_last_updated`)  
) ENGINE=InnoDB AUTO\_INCREMENT=7621125 DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci;

select count(\*) from urls;

±---------+  
| count(\*) |  
±---------+  
| 7620415 |  
±---------+  
1 row in set (28.24 sec)

The indexes are experimental to do the query I am trying to optimize below, and are not permanent to the schema.

here is the query I am trying to optimize:

SELECT urls\_id, urls\_url, urls\_type, urls\_last\_updated FROM urls WHERE urls\_active = ‘1’ and urls\_flag = ‘NONE’ and urls\_hold\_until \< now() and urls\_type = ‘CATEGORY’ ORDER BY urls\_last\_updated LIMIT 100;

Explain on this query:  
±—±------------±------±-----±-------------------±— ------±--------±------±-------±------------------------- —+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±-----±-------------------±— ------±--------±------±-------±------------------------- —+  
| 1 | SIMPLE | urls | ref | get\_urls,get\_urls2 | get\_urls | 1 | const | 418179 | Using where; Using filesort |  
±—±------------±------±-----±-------------------±— ------±--------±------±-------±------------------------- —+

Filesort is a big problem, so if I remove ORDER BY the “Using filesort” goes away. Here is the explain on this query (dropping the ORDER BY)

SELECT urls\_id, urls\_url, urls\_type, urls\_last\_updated FROM urls WHERE urls\_active = ‘1’ and urls\_flag = ‘NONE’ and urls\_hold\_until \< now() and urls\_type = ‘CATEGORY’ LIMIT 100;

±—±------------±------±------±--------------±------- —±--------±-----±--------±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±------±--------------±------- —±--------±-----±--------±------------+  
| 1 | SIMPLE | urls | range | get\_urls2 | get\_urls2 | 9 | NULL | 3815726 | Using where |  
±—±------------±------±------±--------------±------- —±--------±-----±--------±------------+  
1 row in set (0.00 sec)

The first query with the ORDER BY takes 1 minute 38 seconds over 7 million rows. The second query takes 0.01 seconds (with query cache turned off)

How do I get this query optimized, understanding that I MUST have the order by in there, or the equivalent.

Building another table, running periodic summary queries into another table are not acceptable solutions, unfortunately (

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [July 26, 2009, 4:31am UTC](https://forums.percona.com/t/order-by-datetime-still-using-filesort/1219/2 "2009-07-26T04:31:34Z")

</div>

Every part of an index after an inequality is ignored (unless for covering indices).

So the “urls\_hold\_until \< now()” is spoiling your performance. If this is not a very restrictive condition, you should just remove the urls\_hold\_until part from your index.

Your get\_urls index is redundant btw since any prefix of an index can also be used as an index.

---

<div class="post-metadata">

**Author:** ![Karl](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@Karl](https://forums.percona.com/u/Karl)\
**Post date:** [July 26, 2009, 6:16am UTC](https://forums.percona.com/t/order-by-datetime-still-using-filesort/1219/3 "2009-07-26T06:16:52Z")

</div>

Also by using BIGINT all your secondary indexes are larger than they need to be. InnoDB stores the PK for each row in each of your secondary indexes, so by changing it to INT you could decrease your index size.