MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing a query with group by and order by count
Posted by: Rick James
Date: March 22, 2011 07:41PM

These are unlikely to be of any use:
FULLTEXT KEY `Zip_2` (`Zip`),
FULLTEXT KEY `City_2` (`City`)
since you have indexes on these fields, and any query could simply do
WHERE zip = '12345'
No need for FULLTEXT.

It's doing the best it can: "Using index;"
But it has to read the entire index, do the grouping, then filter out (HAVING), sort, and finally deliver only 200 rows.

You cannot avoid filesort.

How big is SIC? If it cannot be bigger than, say, 40 characters, then do
ALTER TABLE listing MODIFY `SIC` varchar(40) NOT NULL;
That may help some, but not much.

What is the value of key_buffer_size? See:
http://mysql.rjweb.org/doc.php/memory
If the key_buffer is not big enough to cache the entire SIC index, then it will have to hit the disk every time you run the query. Hitting the disk slows things down.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimizing a query with group by and order by count
2691
March 22, 2011 07:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.