# Performance using BETWEEN

**URL:** <https://forums.percona.com/t/performance-using-between/264>\
**Category:** Other MySQL® Questions\
**Created:** [March 28, 2007, 7:47pm UTC](https://forums.percona.com/t/performance-using-between/264 "2007-03-28T19:47:14Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dramsey](https://avatars.discourse-cdn.com/v4/letter/d/b3f665/32.png) [@dramsey](https://forums.percona.com/u/dramsey)\
**Post date:** [March 28, 2007, 7:47pm UTC](https://forums.percona.com/t/performance-using-between/264/1 "2007-03-28T19:47:14Z")

</div>

I’m using a large database (ip2location) with about 5.2 million rows. The first two columns are “ip numbers” denoting a range of IP addresses. Each row contains information about the location (zip code, etc.) where this range of IP numbers is located.

If ip = 71.158.134.217, then ipnumber = 71 \* (256^3) + 158 \* (256^2) + 134 \* 256 + 217.

Simple enough, right? The ip number for this ip is 1201571545. To find a location given this IP number, the query is:

SELECT \* FROM ip2location WHERE 1201571545 BETWEEN ip\_from AND ip\_to;

The query takes about 3.5 seconds to resolve on my machine. That’s too slow. What’s odd is that the time is essentially invariant with:

ALTER TABLE ip2location ADD INDEX ip\_from\_index( ip\_from );

…or the suggested:

ALTER TABLE ip2location ADD PRIMARY KEY( ip\_from, ip\_to);

EXPLAINing the query shows that the indexes are never used-- it’s always a full table search. I’ve tried UNIONs and subqueries and whatnot, and nothing makes a whit of difference in the performance. By comparison, a zip code lookup (with an index on the zip code column) executes in a few milliseconds.

There must be a way to make this query faster. Any ideas?

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 31, 2007, 5:15pm UTC](https://forums.percona.com/t/performance-using-between/264/2 "2007-03-31T17:15:38Z")

</div>

The problem you have is that in your query you are limiting a const value between two columns.

columnA \> const AND columnB \< const

Not a column value between two const values.

columnA \> const1 AND columnA \< const2

And I think that the problem is that the optimizer chooses a index range scan since you have an expression that doesn’t close in between two values.

Check out my post #37 on this forum:  
[http://forums.devshed.com/showpost.php?p=1749143&postcou](http://forums.devshed.com/showpost.php?p=1749143&postcou) nt=37

It was the same problem as you had and I solved with a sub-select that first finds the start\_ip and then uses the combined index to verify that the end\_ip is bigger than the ip address you searched on.