I have the following table (userid isn’t unique and userid may have more than one subject):
userid int(10) unsigned NOT NULL,
subject varchar(100) NOT NULL),
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;
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!