Topic: 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

Code: [Select]

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

Code: [Select]

# 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.