Hi,
I have the following table (userid isn’t unique and userid may have more than one subject):
my_test
(
userid
int(10) unsigned NOT NULL,
subject
varchar(100) NOT NULL),
KEY ind_subject
(subject
),
KEY ind_userid
(userid
),
KEY ind_userid_subject
(userid
,subject
)
ENGINE=MyISAM DEFAULT CHARSET=latin1 |
I need to perform the following query, which is very slow:
select subject, count(*) AS Number from (select DISTINCT userid,subject from my_test) AS FILTERED GROUP BY subject ORDER BY Number DESC;
However, the following two queries are very,very fast:
select DISTINCT userid,subject from my_test;
and
select subject, count(*) AS Number from my_test AS FILTERED GROUP BY subject ORDER BY Number DESC;
What is the problem with my inner select query? How I make it faster?
Thanks a lot!
Vadim