Having problems with ORDER BY COUNT
Hi, I could use some help optimising a query if at all possible.
The query is below:
SELECT t_id, COUNT(*) AS cnt
FROM table
WHERE u_id
IN (
SELECT u_id
FROM table
WHERE t_id = $var
)
GROUP BY t_id
ORDER BY cnt DESC
The part that slows it all up is the "ORDER BY cnt DESC". When I run the query with the line it takes well over 0.5 seconds, without that line it completes in under 0.01 seconds.
I've got the following indexes:
u_t = t_id, u_id
u_id = u_id
Explain displays the following:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY snatched index NULL u_t 8 NULL 145771 Using where; Using index; Using temporary; Using f...
2 DEPENDENT SUBQUERY snatched ref u_t,u_id u_t 8 const,func 1 Using where; Using index
I've tried changing the sort_buffer_size from 1M to 16M and also the read_buffer_size from 4M to 16M in my.cnf but they make no noticeable diffenence.
I can understand why it can't use the index for "ORDER BY cnt" because it is only using data from a part of the table. I'm just not sure what I can do from here to speed it up.
I'm using MyISAM and running mySQL 4.1 on FreeBSD.
Any help is appreciated, let me know if I've missed anything out.
Subject
Views
Written By
Posted
Having problems with ORDER BY COUNT
14354
April 29, 2006 08:56AM
5141
January 15, 2007 03:35AM
4766
January 15, 2007 08:28AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.