Will Indexing Binary Columns Improve Performance for SQL WHERE BETWEEN statements?

Question

I have a table that has a binary column that represents IP Address data. One of the queries perform a BETWEEN comparison on that binary column.

If I index that column, will performance improve for the BETWEEN comparison in the SQL Statement?

Answer 1

You may use EXPLAIN SELECT ... query to check which index (if any) is used for this comparison.

Answer 2

Generally speaking, a question like this can only really be answered by trying it out on your particular database and data set. The query optimizer is going to make the decision to use (or not use) the index based on a lot of factors, including the size of the table, index statistics, how many disk pages the resulting set would touch, etc.

Theoretically, it could improve performance. The main issue is whether the index can prevent disk IOs (that's the main issue in pretty much all database performance questions, as it turns out, because disk IO is at least an order of magnitude slower than memory access). So, if looking at the index could tell the query optimizer that your query will only touch records on a subset of disk pages, the query optimizer would be able to just read those disk pages. If that's a small subset of the whole table, it'll (probably) be faster than a table scan.

The thing is, this "theoretical" answer leaves out a zillion details that affect the performance in major ways, like caching (how many of those disk pages are already in memory anyway?). It also depends on whether the values you're "between-ing" cover a wide range or a narrow range. So, finding things between 127.0.0.1 and 127.0.0.10 is likely to touch a small number of disk pages (assuming there's not data skew), so an index would really help. Whereas, finding things between 0.0.0.0 and 255.255.255.255 is going to include all the records anyway, and an index won't do squat except take up room and cycles.

So in short, you've got to just try it and see. Set yourself a little experiment with two versions of the table, one indexed and one not, and see if a few typical "between" queries use the index to gain a speed advantage or not. Ideally, try it in a stress situation (like, run it a bunch of times, ideally concurrently) and that'll tell you more about real world performance.

Maybe. :)

Answer 3

In this example...

SELECT * FROM MyTable WHERE BinaryCol BETWEEN x and y

• the SELECT * could mean that the index is ignored, especially if the x/y cover a large proportion of the table
• If x/y are very selective, then it would cause a bookmark/RID lookup (especially SQL Server) to the clustered index.
• this applies to an index on BinaryCol alone or if it's covered

And is this example...

SELECT BinaryCol, AnotherCol, YetAnotherCol FROM MyTable WHERE BinaryCol BETWEEN x and y

• An index on BinaryCol that includes AnotherCol and YetAnotherCol will be covered and it's highly likely the index will be used
• if the x/y cover a very large proportion of the table, then maybe not

Unfortunately, "it depends"