MySQL Forums
Forum List  »  Performance

Re: Wierd index behaviour
Posted by: Felix Geerinckx
Date: September 15, 2005 01:55AM

Boris Belitsky wrote:

> [explanation about range query snipped]

> Can anybody give any ideas on why this thing happens and what could be done to get rid of it?

This is the black magic of the range query optimizer, which has been discussed here many times
(see e.g. http://forums.mysql.com/read.php?24,42553,42553#msg-42553 for a recent thread).

You only have one (composite) index on (ip_from, ip_to), that's why the value of b won't play an important role, if any at all.

You can give the optimizer more options to work with if you include a UNIQUE INDEX on (ip_to, ip_from):

ALTER TABLE IP2Country ADD UNIQUE to_from (ip_to, ip_from);

That way the value of b will play a role, and hopefully the optimizer will pick the index which results in the smallest number of rows to be examined.

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote


Subject
Views
Written By
Posted
1961
September 14, 2005 11:35PM
Re: Wierd index behaviour
1268
September 15, 2005 01:55AM
1267
September 15, 2005 02:11AM
1329
September 15, 2005 02:36AM


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.