| Query with inner SELECT is slow [message #74] |
Thu, 17 August 2006 11:57  |
 vadim1508
Messages: 9
Registered: August 2006 |
Junior Member |
|
|
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
|
|
|
|
|
|
| Re: Query with inner SELECT is slow [message #79 is a reply to message #78 ] |
Fri, 18 August 2006 06:24  |
vadim1508
Messages: 9
Registered: August 2006 |
Junior Member |
|
|
1. Is there reason that I can't remove the "subject" index, but can remove the "userid" index because in my composite index I have (userid,subject) and the userid, which is in first position serves also as single userid index?
2. Your select count(distinct user_id),subject from table group by subject does exactly what I need, but unless I added an additional index: (subject,userid) it was also very slow. As you are saying in your post: "Order of columns in index is significant, index (A,B) is not duplicate to index (B,A)". How I determine the order of composite index?
Thank you!
|
|
|
| Re: Query with inner SELECT is slow [message #80 is a reply to message #79 ] |
Fri, 18 August 2006 06:37 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
1.
Yes userid can be removed because it is prefix of (userid,subject)
2. I did not say it would be fast  I just shown how query should look. Yes this querty benefits of (subject,userid) key because it can perform index scan in this case while if there is index only on subject row reads would be required
About choosing (A,B) or (B,A) you just look at your queries and see which index they need - it may be one, another or both depending on queries you run.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
