On Wed, Oct 19, 2011 at 05:09:13PM +1100, Wayne W wrote: > Hi, > > I asked this question over on stackoverflow - basically I have a query > and when using EXPLAIN I see that around 2400 rows are still being > scanned. I'd added various indexes but it cannot make it perform any > better. > > I would appreciate if anyone has any further ideas? > > http://stackoverflow.com/questions/7793393/what-can-i-do-to-make-this-sql-more-effecient-table-has-850k-rows >
Create index on (importance, company_id) (or (company_id,importance), should not matter). When that index is present, the query should use ref access on it using both key parts (if by some crazy reason it doesn't, use FORCE INDEX to make it to). This way, you will be scanning as few rows as possible. Also, check if the EXPLAIN has "Using index". If it doesn't, add `id` to the index, i.e. use INDEX (importance, company_id, id) That way, the query will be only using index, which will give the best possible speed. BR Sergey -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
