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.

Hi Joseph, can you edit the question and list the original index(es) on each table including which column(s) are in each and in what order? — TetonSig
– TetonSig, Commented Dec 26, 2011 at 0:14
Also, can you clarify what the query is supposed to return? It looks to me that for a given user, you'd like to show the city relating to any suspect postal code that falls within a range assigned to that user. And you'd also like to see which range the city falls into. I ask because knowing what you want will let us know what options we have for structuring the query. — TetonSig
– TetonSig, Commented Dec 26, 2011 at 0:18
Hi, I've edited the post, hope there ar emore useful informations now. — Joseph
– Joseph, Commented Dec 28, 2011 at 11:09

Oleg Dok · Accepted Answer · 2011-12-25 19:43:12Z

2

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

edited Dec 25, 2011 at 19:43

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

answered Dec 25, 2011 at 19:30

pseudocode

3611 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vadim Samokhin Over a year ago

Inner joined is usually slower than left one, and SELECTs in sub-queries do not use index at all. Joseph, have you tried it?

Joseph Over a year ago

Thanks, it gives me an error: Operand should contain 1 column(s) It i probably between part? I'm not sure how to rewrite this.

golimar · Accepted Answer · 2011-12-25 19:03:40Z

0

It's using only one index, and not for the fields involved in the join. Try creating an index for the start and end fields, or using >= and <= instead of BETWEEN

answered Dec 25, 2011 at 19:03

golimar

2,5881 gold badge24 silver badges37 bronze badges

1 Comment

Joseph Over a year ago

Hi, both start and end fields are indexed (sorry that I didn't mentioned that), I've tried <= >= but it has same result.

Amadan · Accepted Answer · 2011-12-25 19:07:20Z

0

Not 100% sure, but this might be relevant:

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

So try testing with LIMIT, and if it uses the index then, you found your cause.

answered Dec 25, 2011 at 19:07

Amadan

200k23 gold badges252 silver badges321 bronze badges

1 Comment

Joseph Over a year ago

Hi, I've tried to add LIMIT with quite small values like 10 and it didn't have any effect :/

Assaf Karmon · Accepted Answer · 2011-12-25 19:09:48Z

0

I have to say I'm a little confused by your table naming convention, I would expect the "suspect" table to have a user_id not the postal_code, but you must have your reasons. If you were to leave this query as it is, you can add an index on postal_code (star,end) to avoid the complete table scan.

answered Dec 25, 2011 at 19:09

Assaf Karmon

9231 gold badge10 silver badges24 bronze badges

3 Comments

Joseph Over a year ago

Hi, postal_codes table contains 3 fields: user_id, start, end so it basically keeps info about various postal code ranges for specified user_id.

Assaf Karmon Over a year ago

"Hi, I already have a primary key containing user_id, start, end". the problem is that mysql can only use the prefix of the index. User id is not in the where clause so your index is not being used. try having an index that start with (start,end ,...).

Joseph Over a year ago

Hi, I've added the indexes, but still same result.

codemaster · Accepted Answer · 2011-12-25 19:36:12Z

0

I think you can restructure your query like following,

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

your query is not picking up the index on s table because of left join and your between condition. Having an Index in your table doesn't necessarily mean that it will be used in all the queries.

answered Dec 25, 2011 at 19:36

codemaster

5622 gold badges6 silver badges16 bronze badges

4 Comments

Joseph Over a year ago

Thanks, I've used your query, but it took the same time :/ SELECT DISTINCT s.city, pc1.start, pc1.end FROM (SELECT pc.start, pc.end FROM postal_codes pc WHERE pc.user_id = "username") as pc1, suspects s WHERE s.postalcode BETWEEN pc1.start AND pc1.end ORDER BY pc1.start

codemaster Over a year ago

Joseph- time to evaluate the query also depend on what is data distribution in the table.

codemaster Over a year ago

I meant to say that what data is stored in your table also affects the query. like you might have far more postalcodes between start and end values in some cases and less values in some other cases. although 0.5 doesn't seem to be a bad time if you have 340 000 entries in suspect table.

Joseph Over a year ago

Oh, I understand now. Yes, I've tried to test this on +- same amount of possible results. Yes, it isn't a bad result for one user, but I'm using this to display results for about 25 users at the time :/

Vadim Samokhin · Accepted Answer · 2011-12-25 19:45:28Z

0

Try FORCE INDEX.

answered Dec 25, 2011 at 19:45

Vadim Samokhin

3,4766 gold badges44 silver badges70 bronze badges

1 Comment

Joseph Over a year ago

Hi, tanks for reply. I've forced index on suspects table (postalcode field) but no effect, I tried to force indexes also on start & end on postal_codes table but the query took 5x more.

Collectives™ on Stack Overflow

Optimize mysql query using indexes

6 Answers 6

2 Comments

1 Comment

1 Comment

3 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

1 Comment

3 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related