Have Geek Brain

Will indexing binary columns improve the performance of SQL WHERE BETWEEN queries?

I have a table with a binary column representing IP address data. One of the queries performs a BETWEEN comparison on this binary column.

If I index this column, does the performance improve for the BETWEEN comparison in the SQL statement?

+2


source to share


3 answers


In this example ...

SELECT * FROM MyTable WHERE BinaryCol BETWEEN x and y
  • SELECT *

    may mean that the index is being ignored, especially if x / y covers most of the table
  • If x / y is very selective, it will bookmark / look up RIDs (especially SQL Server) into the clustered index.
  • it refers to the index only for BinaryCol or if it covers

And this example ...



SELECT BinaryCol, AnotherCol, YetAnotherCol FROM MyTable WHERE BinaryCol BETWEEN x and y
  • The BinaryCol index, which includes AnotherCol and YetAnotherCol, will be covered , and most likely the index will be used
  • if x / y covers a very large part of the table, then it may not

Unfortunately, "it depends"

+4


source


You can use a query EXPLAIN SELECT ...

to check which index (if any) is used for this comparison.



+1


source


Generally speaking, a question like this can only be answered by trying it on your specific database and dataset. The query optimizer is about to make a decision to use (or not use) an index based on many factors, including table size, index statistics, number of disk pages the result set hit, and so on.

In theory, this could improve performance. The main issue is whether the index can prevent disk IOs (which is the main issue in almost all database performance issues, as it turns out, since disk IO is at least an order of magnitude slower than memory access). So if you look at the index, you can tell the query optimizer that your query will only be about records on a subset of disk pages, the query optimizer can just read those disk pages. If it is a small subset of the entire table, it will (probably) be faster than a table scan.

The point is that this "theoretical" answer does not include details that affect performance in a big way, such as caching (how many of these disk pages are already in memory?). It also depends on whether the values ​​you are "among yourself" cover a wide range or a narrow range. So looking for things between 127.0.0.1 and 127.0.0.10 will probably touch a small number of disk pages (assuming no data corruption), so the index will really help. Whereas looking for things between 0.0.0.0 and 255.255.255.255 will include all entries anyway, and the index will not take squats apart from occupied room and cycles.

In short, you just have to try and see. Do a little experiment with two versions of a table, one indexed and one not, and see if a few typical "in between" queries use an index to gain a speed advantage or not. Ideally, try it in a stressful situation (for example, run it multiple times, ideally at the same time) and it will tell you more about the real world of performance.

May be.:)

+1


source






More articles:


All Articles