MySQL Forums
Forum List  »  Optimizer & Parser

Re: two column int index not used correctly
Posted by: Alexander Berl
Date: June 16, 2011 08:58AM

Hi Jørgen,

thank you very much for your explanation. That really shed some light on this mistery and makes complete sense.

Since using FORCE INDEX ip_start_int didn't really help that much yet, as MySQL still had to scan that many index entries, I helped it by adding another "backward" index to the table (ip_end_int, ip_start_int, country).
Adding a LIMIT 1 to the query also helped, so it can immediately stop when finding the first match (which *we* know is the only one, since the ip ranges are not overlapping, but mysql doesn't).

This way, the optimizer can choose to scan down or up depending on the ip-value to search for, and hence at most has to scan half the table, effectively meaning that the max query time for IP lookup is ~halved.
For the query in question, it now only has to scan 21788 index entries instead of 110000+, which helps a lot.

Also, to my understanding, since the value to return (country) is also in the index, MySQL doesn't have to fetch the value from the table, but directly takes it from the index, yes? Not that it has much impact in this case, but I just want to understand the workings a bit more.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.