Adding WHERE clause makes query really slow

Consider the following two queries:

select a.*, c.*
from account a
join customer c on a.customer_id = c.id
join import i on a.import_id = i.id
join import_bundle ib on i.import_bundle_id = ib.id

and

select a.*, c.*
from account a
join customer c on a.customer_id = c.id
join import i on a.import_id = i.id
join import_bundle ib on i.import_bundle_id = ib.id
where ib.id = 8

The first query is fast and the second one is super slow. Any idea why? I'm guessing I need an index or something but I don't 开发者_开发知识库understand how indexes work. I'm using MySQL.

Here's what happens if I do an EXPLAIN on the second query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
 1  SIMPLE  ib  const   PRIMARY     PRIMARY     8   const   1   Using index
 1  SIMPLE  c   ALL     PRIMARY         144858  
 1  SIMPLE  a   ref     fk_account_customer_id,fk_account_import_id     fk_account_customer_id  8   mcif.c.id   2   
 1  SIMPLE  i   eq_ref  PRIMARY,import_bundle_id    PRIMARY     8   mcif.a.import_id    1   Using where

I don't know how to interpret that, though.

Edit: this is what I ended up using:

select a.*,
       c.*
  from account a
  join customer c on a.customer_id = c.id
  join (select id,
               import_bundle_id
          from import
         where import_bundle_id = 8) i on a.import_id = i.id
  join import_bundle ib on i.import_bundle_id = ib.id

Adding an index on import_bundle.id didn't do anything.

• Regarding performance, in your query, you really need a.* and c.*?

• So, the use of an index does not improve enough. I'm not familiar with mysql but could you try a join with a subquery like this?

    select a.*, c.*
    from account a
    join customer c on a.customer_id = c.id
    join 
    (    
        SELECT id, import_bundle_id FROM import WHERE id = 8
    ) as i on a.import_id = i.id
    join import_bundle ib on i.import_bundle_id = ib.id
    where ib.id = 8

• Probably, the best index are: An index on import.import_bundle_id and another index on import_bundle.id.

Use EXPLAIN http://dev.mysql.com/doc/refman/5.0/en/explain.html to understand the index can improve your search.

Here how index works http://www.tizag.com/mysqlTutorial/mysql-index.php

The latter query forces MySQL to find the record(s) within the result set with ib.id = 8.

If you add an index to import_bundle.id, then MySQL will be able to quickly find the corresponding record, rather than having to check them all.

A database index is just like one in a textbook, rather than looking through every page, you go to the index at the back, find the page number(s) for what you are looking for and go straight there.

I'm not familiar with mysql specifically, but an index on ib.id would almost certainly be helpful. Any fields you use in a JOIN or WHERE clause should generally be indexed. Also, you can try filtering on i.import_bundle_id instead of ib.id and see if that helps.

Indexes in general serve to make it faster to find information. Instead of having to look through every item scattered far and wide over your entire database to find the right one, they can use a hash table or some similar method to narrow down where they have to look, possibly narrowing it down to precisely the right record. Wikipedia explains it far better than I can. :)

The other answers are pointing you in the right direction as far as an index on ib.id. However, ib.id looks like a primary key (PK). Is it set as a PK in your database? If so, it should automatically have gotten an index by virtue of being a PK. If it's not set as a PK, and it is indeed a column that should be, you're going to have more problems with your database than just performance.

In short, if ib.id ought to be a primary key, make it one, and your performance should improve because it will automatically get indexed (and you won't need to worry about separately adding any indexes to it).