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

The suspicious table contains about 340,000 records, there is an index in the zip code, I have several users, but this separate query takes about 0.5 s, when I run this SQL with an explanation, I get something like this: http: / /my.jetscreenshot.com/7536/20111225-myhj-41kb.jpg - do these NULLs mean that the query does not use an index? The index is BTREE, so I think it should work a little faster.

could you help me? If you need any other information, 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 several ranges of zip codes, so it looks like this:

user_id | start | end

Than I have a table of suspects, where each suspect has an address (which contains a zip code), so in this query I try to get a range of zip codes - beginning and end, as well as the name of the city in this range.

Hope this helps.

Answer 1

Whenever a left join is used, all the records of the first table are selected, not the index-based selection. I would suggest using an inner join. Something like in the following 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

Answer 2

, , . >= <= BETWEEN

Answer 3

100%, this :

MySQL , . , , - , MySQL . ( , , , .) , LIMIT , MySQL , .

, LIMIT, , .

Answer 4

, , , "" user_id, _, . , , postal_code (star, end), .

Answer 5

, , ,

SELECT DISTINCT s.city, pc1.start, pc1.end FROM 
(SELECT pc.start and pc.end from postal_codes pc where pc.user_id = "username") as pc1,    Suspect s
WHERE s.postalcode BETWEEN pc1.start, pc1.end ORDER BY pc1.start

s - . , .

Answer 6

FORCE INDEX.