Optimize mysql query using indexes

Question

I have a problem with this query:

SELECT DISTINCT s.city, pc.start, pc.end 
FROM postal_codes pc LEFT JOIN suspects s ON (s.postalcode BETWEEN pc.start AND      pc.end) 
WHERE pc.user_id = "username" 
ORDER BY pc.start

Suspect table has about 340 000 entries, there is a index on postalcode, I have several users, but this individual query takes about 0.5s, when I run this SQL with explain, I get something like this: http://my.jetscreenshot.com/7536/20111225-myhj-41kb.jpg - does these NULLs mean that the query isn't using index? The index is a BTREE so I think this should run a little faster.

Can you please help me with this? If there are any other informations needed just let me know.

Edit: I have indexes on suspects.postalcode, postal_codes.start, postal_codes.end, postal_codes.user_id.

Basically what I'm trying to achieve: I have a table where each user ID has multiple postalcode ranges assigned, so it looks like:

user_id | start | end

Than I have a table of suspects where each suspect has an address (which contains a postalcode), so in this query I'm trying to get postalcode range - start and end and also name of the city in this range.

Hope this helps.

Answer 1

Whenever left join is used all the records of the first table are picked up rather than the selection on the basis of index. I would suggest to using an inner join. Something like in the below query.

select distinct 
  s.city, 
  pc.start, 
  pc.end 
from postal_codes pc, suspect s 
where 
  s.postalcode between (select pc1.start, pc1.end from postal_code pc1 where pc1.user_id = "username" ) 
  and pc.user_id = "username"
order by pc.start