# Query with inner SELECT is slow

**URL:** https://forums.percona.com/t/query-with-inner-select-is-slow/66
**Category:** Other MySQL® Questions
**Created:** [August 17, 2006, 10:27am UTC](https://forums.percona.com/t/query-with-inner-select-is-slow/66 "2006-08-17T10:27:24Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![vadim1508](https://avatars.discourse-cdn.com/v4/letter/v/8491ac/32.png) [@vadim1508](https://forums.percona.com/u/vadim1508)
#### Post date: [August 17, 2006, 10:27am UTC](https://forums.percona.com/t/query-with-inner-select-is-slow/66/1 "2006-08-17T10:27:24Z")

</div>

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

---

<div class="post-metadata">

### Author: ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)
#### Post date: [August 17, 2006, 10:47am UTC](https://forums.percona.com/t/query-with-inner-select-is-slow/66/2 "2006-08-17T10:47:16Z")

</div>

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

[http://www.mysqlperformanceblog.com/2006/08/17/duplicate-ind](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.

---

<div class="post-metadata">

### Author: ![vadim1508](https://avatars.discourse-cdn.com/v4/letter/v/8491ac/32.png) [@vadim1508](https://forums.percona.com/u/vadim1508)
#### Post date: [August 17, 2006, 11:57am UTC](https://forums.percona.com/t/query-with-inner-select-is-slow/66/3 "2006-08-17T11:57:51Z")

</div>

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