Slow GROUP BY

[sastro]

# Time: 100312  3:01:23

# User@Host: admin[admin] @ localhost []

# Query_time: 14  Lock_time: 0  Rows_sent: 20  Rows_examined: 1214165

SELECT ld_keyword, ld_subkeyword, ld_desc, ld_no_file, ld_visit, ld_url

FROM latestdownload

WHERE ld_stat = '1'

GROUP BY ld_keyword

ORDER BY ld_id DESC

LIMIT 0 , 20;

 

It took 14 seconds to get 20 records from 1214165 records.

How to optimize the query?

 

Thanks in advanced

[sastro]

Anyone?

[fenway]

Show us EXPLAIN output.

[sastro]

Show us EXPLAIN output.

 

Here it is

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE latestdownload ALL NULL NULL NULL NULL 1222574 Using where; Using temporary; Using filesort

[fenway]

Why don't you have any indexes???

[sastro]

I added index to ld_keyword

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	latestdownload 	index 	NULL 	PRIMARY 	4 	NULL 	1319942 	Using where; Using temporary

 

And now it took 22 seconds for that query

 

# Time: 100320 15:11:52
# User@Host: root[root] @ localhost []
# Query_time: 22  Lock_time: 0  Rows_sent: 20  Rows_examined: 1411540
SELECT ld_keyword, ld_subkeyword, ld_desc, ld_no_file, ld_visit, ld_url
FROM latestdownload
WHERE ld_stat = '1'
GROUP BY ld_keyword
ORDER BY ld_id DESC
LIMIT 0 , 20;

 

Any idea?

 

[sastro]

Server spec :

Xeon 3050 Dual Core

RAM/Memory: 2GB DDR2

Operating System: CentOS 5 - 64-BIT

MySQL Ver.: 5.0.89-community-log

 

>top

top - 18:37:22 up 23:24,  2 users,  load average: 0.09, 0.09, 0.09

Tasks: 149 total,  2 running, 147 sleeping,  0 stopped,  0 zombie

Cpu(s):  3.9%us,  0.7%sy,  0.0%ni, 94.0%id,  1.5%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:  2037776k total,  1302420k used,  735356k free,  103440k buffers

Swap:  4088500k total,    4640k used,  4083860k free,  752000k cached

 

>more /etc/my.cnf

[mysqld]

skip-innodb

skip-locking

log-error=/var/log/mysql/mysql_error.log

set-variable = max_connections=100

query_cache_size =4194304

query_cache_type=1

query_cache_limit=2097152

sort_buffer_size =2097152

read_rnd_buffer_size = 262144

join_buffer_size=131072

key_buffer_size = 209715200

thread_cache_size = 4

table_cache = 128

log-slow-queries = /var/log/mysql/mysql-slow.log

long_query_time = 1

safe-show-database

[fenway]

What you actually want is an index on ( ld_id, ld_keyword )... we can sort of the order by after.