why is this query so slow with MySQL?

Question

Yesterday I found a slow query running on the server(this query costs more than 1 minute).It looks like this:

select a.* from a 
left join b on a.hotel_id=b.hotel_id and a.hotel_type=b.hotel_type
where b.hotel_id is null

There are 40000+ rows in table a and 10000+ rows in table b.An unique key had already been created on columns hotel_id and hotel_type in table b like UNIQUE KEY idx_hotel_id (hotel_id,hotel_type).So I used the explain keyword to check the query plan on this sql and I got a result like the following:

                type            key                             rows
1   SIMPLE  a   ALL     NULL    NULL            NULL    NULL    36804   
1   SIMPLE  b   index   NULL    idx_hotel_id    185     NULL    8353    Using where; Using index; Not exists

According to the reference manual of MySQL, when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index the join type will be "eq_ref".See the second row of the query plan,the value of column type is "index".But I really had en unique index on hotel_id and hotel_type and both the two columns were used by the join.The join type "ef_ref" is more efficient than the join type "ref" and "ref" is more efficient than "range"."index" is the last join type wo wanna hava except "ALL".This is what I'm confused about and I wanna know why the join type here is "index". I hope I describe my question clear and I'm looking forward to get answers from you guys,thanks!

Answer 1

Where Is Null checks can be slow, so maybe it is that.

select * from a 
where not exists ( select 1 from b where a.hotel_id=b.hotel_id and a.hotel_type=b.hotel_type )

Also: how many records are you returning? If you are returning all 36804 records this could slow things down as well.