MySQL Performance: MySQL => ORDER BY ..LIMIT using indexes still slow

Members

Help

Home

Home » Performance » MySQL » ORDER BY ..LIMIT using indexes still slow

Show: Today's Messages :: Show Polls :: Message Navigator

ORDER BY ..LIMIT using indexes still slow [message #2890]

Fri, 04 April 2008 10:53

gorzo
Messages: 2
Registered: April 2008

Junior Member

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

[Updated on: Fri, 04 April 2008 11:11]

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2891 is a reply to message #2890 ]

Fri, 04 April 2008 12:14

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

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_title
from company_message cm, community c
where cm.community_id IN (SELECT community_id FROM users_community_role ucr WHERE ucr.users_id=461)
and cm.community_id=c.community_id
order by cm.company_message_id
desc 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... Exclamation

[Updated on: Fri, 04 April 2008 12:16]

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2895 is a reply to message #2891 ]

Fri, 04 April 2008 13:20

gorzo
Messages: 2
Registered: April 2008

Junior Member

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;

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2905 is a reply to message #2890 ]

Sat, 05 April 2008 09:12

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

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_title
FROM company_message cm, community c
WHERE cm.community_id IN (SELECT community_id FROM users_community_role ucr WHERE ucr.users_id=461)
AND cm.community_id=c.community_id
ORDER BY cm.company_message_id
DESC LIMIT 5;

Hope it works Smile

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2950 is a reply to message #2905 ]

Mon, 14 April 2008 11:47

hairroot
Messages: 7
Registered: April 2008

Junior Member

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.

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2951 is a reply to message #2950 ]

Mon, 14 April 2008 11:53

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

Not sure about your case Exclamation

But there should be an index on (md5, mirrors)!?

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2952 is a reply to message #2951 ]

Mon, 14 April 2008 14:05

hairroot
Messages: 7
Registered: April 2008

Junior Member

yes, there is a index created with(md5, mirrors).

here is the explain result:

1 PRIMARY FileMirrors index \N NewIndex1 5 \N 1010 Using where; Using index

2 DEPENDENT SUBQUERY MD5Keyword index_subquery NewIndex1,NewIndex2 NewIndex1 152 func 2 Using where

see, the filesort has been got ridden of.
But it is still slow.

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2954 is a reply to message #2952 ]

Tue, 15 April 2008 03:53

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

can you let me know some more information?

what table type are you using for the table?
if its MyISAM, please consider to optimize the key_buffer_size, join_buffer_size.
if its InnoDB, innodb_buffer_pool_size should be considered.

Hope this help.

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2955 is a reply to message #2954 ]

Tue, 15 April 2008 04:45

hairroot
Messages: 7
Registered: April 2008

Junior Member

hi, safari, thanks for your reply

The table DDL:
CREATE TABLE `MD5Keyword_new` (
`md5` varchar(50) NOT NULL,
`keyword` varchar(50) DEFAULT NULL,
KEY `NewIndex1` (`md5`),
KEY `NewIndex2` (`keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `FileMirrors` (
`md5` varchar(50) NOT NULL,
`mirrors` int(11) DEFAULT '0',
PRIMARY KEY (`md5`),
KEY `NewIndex1` (`mirrors`),
KEY `NewIndex2` (`md5`,`mirrors`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 facts need to be mentioned:
1. there are 380,000 records in MD5Keyword_new
2. there are 30,000 records in FileMirrors

the problem is,
when I executing the following query, it takes 30+ seconds.

select t1.*, t2.mirrors from MD5Keyword_new t1, FileMirrors t2 where t1.keyword = 'andy' and t2.md5=t1.md5 order by t2.mirrors desc limit 0, 10

explain it returns:

1	SIMPLE	t1	ref	NewIndex1,NewIndex2	NewIndex2	153	const	3512	Using where; Using temporary; Using filesort
1	SIMPLE	t2	eq_ref	PRIMARY,NewIndex2	PRIMARY	152	MazeCommunity_Final.t1.md5	1

the FileMirrors.mirrors index seems not being used, so I tested another query:

select * from FileMirrors where md5 in (select md5 from MD5Keyword_new where keyword='andy') order by mirrors limit 0, 10

explain it returns:

1	PRIMARY	FileMirrors	index	\N	NewIndex1	5	\N	1010	Using where; Using index
2	DEPENDENT SUBQUERY	MD5Keyword_new	index_subquery	NewIndex1,NewIndex2	NewIndex1	152	func	2	Using where

now the index on FileMirrors.mirrors is being used, but unfortunately this query cost even longer time to return.

Any suggestion will be great!
Thank you.

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2956 is a reply to message #2955 ]

Tue, 15 April 2008 06:03

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

got it.

the slowness comes from table MD5Keyword_new, please create an index on (keyword, md5) and use the query:

select * from FileMirrors where md5 in (select md5 from MD5Keyword_new where keyword='andy') order by mirrors limit 0, 10

[Updated on: Tue, 15 April 2008 06:07]

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2957 is a reply to message #2956 ]

Tue, 15 April 2008 09:22

hairroot
Messages: 7
Registered: April 2008

Junior Member

after I created a index(keyword, md5) on MD5Keyword_new, the subquery become faster, the overall query is still slow, Sad

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2958 is a reply to message #2957 ]

Tue, 15 April 2008 10:06

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

as in previous reply,

Quote:

what table type are you using for the table?
if its MyISAM, please consider to optimize the key_buffer_size, join_buffer_size.
if its InnoDB, innodb_buffer_pool_size should be considered.

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2961 is a reply to message #2958 ]

Wed, 16 April 2008 02:18

hairroot
Messages: 7
Registered: April 2008

Junior Member

hi, safari, I am not familiar with the innodb settings, I post it below, can you help to figure it out whether it's suitable?
thank you.

innodb_additional_mem_pool_size	1048576
innodb_autoextend_increment	8
innodb_autoinc_lock_mode	1
innodb_buffer_pool_size	8388608
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	ibdata1:10M:autoextend
innodb_data_home_dir	
innodb_doublewrite	ON
innodb_fast_shutdown	1
innodb_file_io_threads	4
innodb_file_per_table	OFF
innodb_flush_log_at_trx_commit	1
innodb_flush_method	
innodb_force_recovery	0
innodb_lock_wait_timeout	50
innodb_locks_unsafe_for_binlog	OFF
innodb_log_buffer_size	1048576
innodb_log_file_size	5242880
innodb_log_files_in_group	2
innodb_log_group_home_dir	./
innodb_max_dirty_pages_pct	90
innodb_max_purge_lag	0
innodb_mirrored_log_groups	1
innodb_open_files	300
innodb_rollback_on_timeout	OFF
innodb_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	8
innodb_thread_sleep_delay	10000
innodb_use_adaptive_hash_indexes	ON

Report message to a moderator

Re: ORDER BY ..LIMIT using indexes still slow [message #2964 is a reply to message #2961 ]

Wed, 16 April 2008 03:40

safari
Messages: 26
Registered: August 2006
Location: Vietnam

Junior Member

To have better tuning, you have to know about your data and aplication: data size, heavy read or write, ....

The first try is to increase innodb_buffer_pool_size. This can be up to 80% of your system physical RAM, but this still depends on your data.

Using this tool http://rackerhacker.com/mysqltuner/ can help for a start.

Monitor database to assist you in database tuning also can help. Try this http://hackmysql.com/mysqlreport.

PS. one more good resource http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune- in-mysql-server-after-installation/

Regards,
Hung

[Updated on: Wed, 16 April 2008 11:18]

Report message to a moderator

Previous Topic:	Is there a way to control query expiration time in cache?
Next Topic:	index merge union not used in a left join?

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 01:21:15 EDT 2009

Total time taken to generate the page: 0.04912 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.