Query with inner SELECT is slow

[vadim1508]

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

[Peter]

First about table structure - you might want to read my blog article from today:

http://www.mysqlperformanceblog.com/2006/08/17/duplicate-ind exes-and-redundant-indexes/

Speaking about queries - providing EXPLAIN would be helpful.

I guess both of fast queries are indexed, while if you use SELECT in FROM clause you get result set back which does not have any indexes. Also MySQL will normally materialize such subselects in temporary table which also may add significant overhead.

Generally I would avoid subselects in FROM clause unless really needed.

[vadim1508]

Thanks for pointing to you post. According to it seems I can remove my KEY ind_subject (subject) and
KEY ind_userid (userid).

Concerning subselecting: how you would avoid it in my case?

Suppose my data is as follows:

userid subject
1 How to become rich?
1 How to find a love?
1 How to find a love?
3 How to become rich?
3 How to sleep at night?

Desired result:
How to become rich? 2
How to find a love? 1
How to sleep at night? 1

I really appreciate your help!
Vadim