List:General Discussion« Previous MessageNext Message »
From:Sergei Petrunia Date:October 19 2011 10:30am
Subject:Re: Is it possible to make this more efficient?
View as plain text  
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
Thread
Is it possible to make this more efficient?Wayne W19 Oct
  • Re: Is it possible to make this more efficient?Sergei Petrunia19 Oct
    • Re: Is it possible to make this more efficient?Johan De Meersman19 Oct