ORDER BY ..LIMIT using indexes still slow

MySQL & MariaDB Other MySQL® Questions
1

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

2

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…

3

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;

4

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 )

5

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.