# ORDER BY ..LIMIT using indexes still slow **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [April 4, 2008, 9:23am UTC](https://forums.percona.com/t/order-by-limit-using-indexes-still-slow/703 "2008-04-04T09:23:21Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![gorzo](https://avatars.discourse-cdn.com/v4/letter/g/ce7236/32.png) [@gorzo](https://forums.percona.com/u/gorzo)\ **Post date:** [April 4, 2008, 9:23am UTC](https://forums.percona.com/t/order-by-limit-using-indexes-still-slow/703/1 "2008-04-04T09:23:21Z")
Hello, I have a typical 3 table inner join with an ORDER BY and LIMIT clause that runs great for smaller data sets, but when the order by column has \>800,000 rows it is take 2 minutes to complete. The EXPLAIN is showing Using temporary; Using filesort in the first table and I cannot seem to get rid of it. Any help would be great appracited. SQL statement: select distinct cm.message\_datetime, c.keyword, cm.message\_status, cm.num\_recipients, cm.message\_title from users\_community\_role ucr, community c, company\_message cm where ucr.users\_id=461 and ucr.community\_id=c.community\_id and c.community\_id=cm.community\_id order by cm.company\_message\_id desc limit 5; ///////////////// Here is the explain: //////////////// mysql\> explain select distinct cm.message\_datetime, c.keyword, cm.message\_status, cm.num\_recipients, cm.message\_title from users\_community\_role ucr, community c, company\_message cm where ucr.users\_id=461 and ucr.community\_id=c.community\_id and c.community\_id=cm.community\_id order by cm.company\_message\_id desc limit 5\G; \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: ucr type: ref possible\_keys: FK\_users\_community\_role\_1,FK\_users\_community\_role\_2,users\_in d key: users\_ind key\_len: 8 ref: const rows: 98 Extra: Using where; Using index; Using temporary; Using filesort \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: c type: eq\_ref possible\_keys: PRIMARY,community\_id key: community\_id key\_len: 8 ref: ucr.community\_id rows: 1 Extra: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: cm type: ref possible\_keys: comm\_cm\_index,company\_message\_community\_id\_fkey,comm\_cmmsgdt key: comm\_cm\_index key\_len: 9 ref: c.community\_id rows: 59 Extra: Using where 3 rows in set (0.00 sec) ///////////////// Here are the indexes: //////////////////// mysql\> show index from users\_community\_role\G; \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: users\_community\_role Non\_unique: 0 Key\_name: PRIMARY Seq\_in\_index: 1 Column\_name: users\_community\_role\_id Collation: A Cardinality: 4204 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: users\_community\_role Non\_unique: 1 Key\_name: FK\_users\_community\_role\_1 Seq\_in\_index: 1 Column\_name: users\_id Collation: A Cardinality: 89 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: users\_community\_role Non\_unique: 1 Key\_name: FK\_users\_community\_role\_2 Seq\_in\_index: 1 Column\_name: community\_id Collation: A Cardinality: 2102 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: users\_community\_role Non\_unique: 1 Key\_name: users\_ind Seq\_in\_index: 1 Column\_name: users\_id Collation: A Cardinality: 89 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 5. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: users\_community\_role Non\_unique: 1 Key\_name: users\_ind Seq\_in\_index: 2 Column\_name: community\_id Collation: A Cardinality: 4204 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: community Non\_unique: 0 Key\_name: PRIMARY Seq\_in\_index: 1 Column\_name: community\_id Collation: A Cardinality: 986 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: community Non\_unique: 0 Key\_name: community\_id Seq\_in\_index: 1 Column\_name: community\_id Collation: A Cardinality: 986 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: community Non\_unique: 1 Key\_name: community\_company\_id\_fkey Seq\_in\_index: 1 Column\_name: company\_id Collation: A Cardinality: 58 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: community Non\_unique: 1 Key\_name: FK\_community\_2 Seq\_in\_index: 1 Column\_name: default\_users\_id Collation: A Cardinality: 1 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 0 Key\_name: PRIMARY Seq\_in\_index: 1 Column\_name: company\_message\_id Collation: A Cardinality: 854352 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 0 Key\_name: company\_message\_id Seq\_in\_index: 1 Column\_name: company\_message\_id Collation: A Cardinality: 854352 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 0 Key\_name: comm\_cm\_index Seq\_in\_index: 1 Column\_name: community\_id Collation: A Cardinality: 14480 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 0 Key\_name: comm\_cm\_index Seq\_in\_index: 2 Column\_name: company\_message\_id Collation: A Cardinality: 854352 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 5. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 1 Key\_name: company\_message\_mobile\_offer\_id\_fkey Seq\_in\_index: 1 Column\_name: mobile\_offer\_id Collation: A Cardinality: 17 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 6. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 1 Key\_name: company\_message\_community\_id\_fkey Seq\_in\_index: 1 Column\_name: community\_id Collation: A Cardinality: 17 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 7. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 1 Key\_name: company\_message\_user\_request\_id\_fkey Seq\_in\_index: 1 Column\_name: user\_request\_id Collation: A Cardinality: 17 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 8. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 1 Key\_name: comp\_message\_message\_date\_index Seq\_in\_index: 1 Column\_name: message\_datetime Collation: A Cardinality: 34174 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 9. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 1 Key\_name: comm\_cmmsgdt Seq\_in\_index: 1 Column\_name: community\_id Collation: A Cardinality: 407 Sub\_part: NULL Packed: NULL Null: YES Index\_type: BTREE Comment: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 10. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: company\_message Non\_unique: 1 Key\_name: comm\_cmmsgdt Seq\_in\_index: 2 Column\_name: message\_datetime Collation: A Cardinality: 170870 Sub\_part: NULL Packed: NULL Null: Index\_type: BTREE Comment: Thank you in advance and please let me know if I left out any critcal information. Thanks, Brian ---
**Author:** ![safari](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@safari](https://forums.percona.com/u/safari)\ **Post date:** [April 4, 2008, 10:44am UTC](https://forums.percona.com/t/order-by-limit-using-indexes-still-slow/703/2 "2008-04-04T10:44:40Z")
The first optimization I can see is to move the ucr search by users\_id to sub-query as below: select distinct cm.message\_datetime, c.keyword, cm.message\_status, cm.num\_recipients, cm.message\_titlefrom company\_message cm, community cwhere cm.community\_id IN (SELECT community\_id FROM users\_community\_role ucr WHERE ucr.users\_id=461)and cm.community\_id=c.community\_idorder by cm.company\_message\_iddesc limit 5; I could not see why you need a DISTINCT. So please post the tables structure then I can understand more. BTW, it seems that there’re some redundant indexes that could be removed. Await the tables structure… ---
**Author:** ![gorzo](https://avatars.discourse-cdn.com/v4/letter/g/ce7236/32.png) [@gorzo](https://forums.percona.com/u/gorzo)\ **Post date:** [April 4, 2008, 11:50am UTC](https://forums.percona.com/t/order-by-limit-using-indexes-still-slow/703/3 "2008-04-04T11:50:23Z")
Thanks for your reply. We need the DISTINCT because the users\_community\_role table is denormalized. We can have many users\_id - community\_id matching entries. We need this for some other optimizations but it is hurting us here and maybe we need to re-think that. FYI…here are table structures. CREATE TABLE `users_community_role` ( `users_community_role_id` bigint(20) unsigned NOT NULL auto\_increment, `community_id` bigint(20) unsigned NOT NULL default ‘0’, `users_id` bigint(20) unsigned NOT NULL default ‘0’, `uuid` varchar(36) NOT NULL default ‘’, `role_name` varchar(255) default NULL, `group_name` varchar(255) default NULL, `date_created` timestamp NOT NULL default CURRENT\_TIMESTAMP on update CURRENT\_TIMESTAMP, `date_updated` timestamp NOT NULL default ‘0000-00-00 00:00:00’, PRIMARY KEY (`users_community_role_id`), KEY `FK_users_community_role_1` (`users_id`), KEY `FK_users_community_role_2` (`community_id`), CONSTRAINT `FK_users_community_role_1` FOREIGN KEY (`users_id`) REFERENCES `users` (`users_id`), CONSTRAINT `FK_users_community_role_2` FOREIGN KEY (`community_id`) REFERENCES `community` (`community_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `community` ( `community_id` bigint(20) unsigned NOT NULL auto\_increment, `keyword` varchar(100) default NULL, PRIMARY KEY (`community_id`), UNIQUE KEY `community_id` (`community_id`), KEY `FK_community_2` USING BTREE (`default_users_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 10240 kB; CREATE TABLE `company_message` ( `company_message_id` bigint(20) unsigned NOT NULL auto\_increment, `uuid` varchar(36) default NULL, `status` tinyint(4) default ‘0’, `community_id` bigint(20) unsigned default NULL, `message_title` varchar(250) default NULL, `message_text` text NOT NULL, `message_status` varchar(50) default NULL, `message_datetime` timestamp NOT NULL default ‘0000-00-00 00:00:00’, `num_recipients` bigint(20) unsigned default NULL, PRIMARY KEY (`company_message_id`), UNIQUE KEY `company_message_id` (`company_message_id`), KEY `company_message_community_id_fkey` (`community_id`), CONSTRAINT `company_message_community_id_fkey` FOREIGN KEY (`community_id`) REFERENCES `community` (`community_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE=InnoDB DEFAULT CHARSET=latin1; ---
**Author:** ![safari](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@safari](https://forums.percona.com/u/safari)\ **Post date:** [April 5, 2008, 7:42am UTC](https://forums.percona.com/t/order-by-limit-using-indexes-still-slow/703/4 "2008-04-05T07:42:52Z")
Yes, please do as follow: - table `users_community_role`: replace key `FK_users_community_role_1`(users\_id) by (users\_id, community\_id) - table `company_message`: - remove UNIQUE KEY `company_message_id` (`company_message_id`) as it’s redundant - replace key `company_message_community_id_fkey` (`community_id`) by (community\_id, company\_message\_id) - try the query below: SELECT cm.message\_datetime, c.keyword, cm.message\_status, cm.num\_recipients, cm.message\_titleFROM company\_message cm, community cWHERE cm.community\_id IN (SELECT community\_id FROM users\_community\_role ucr WHERE ucr.users\_id=461)AND cm.community\_id=c.community\_idORDER BY cm.company\_message\_idDESC LIMIT 5; Hope it works ) ---
**Author:** ![hairroot](https://avatars.discourse-cdn.com/v4/letter/h/41988e/32.png) [@hairroot](https://forums.percona.com/u/hairroot)\ **Post date:** [April 14, 2008, 10:17am UTC](https://forums.percona.com/t/order-by-limit-using-indexes-still-slow/703/5 "2008-04-14T10:17:35Z")
hi, guys, I am glad to join this thread, since I’ve encountered with the same problem. to safari: I’ve also tested your suggested sql, instead of using a filesort, mysql now use index on the order by column, but…It’s still SLOW. select \* from FileMirrors where md5 in(select md5 from MD5Keyword where keyword=‘mp3’) order by mirrors desc limit 1000, 10 in the upper sql, subquery from MD5Keyword produces about 30,000 records, I guess this is the main reason that produces poor performance.