AR7 AR7 - 1 year ago 72
SQL Question

Why do functions on columns prevent the use of indexes?

On this question that I asked the other day I got the following comment.

In almost any database, almost any function on a column prevents the use of indexes. There are exceptions here and there, but in general, functions prevent the use of indexes

I googled around and found more mentions of this same behavior, but I had trouble finding something more in depth than what the comment already told me.

Could someone elaborate on why this occurs, and perhaps strategies for avoiding it?

Answer Source

An index in its most basic form is just the sorted column data, making it easy to look up by some value. For example, a textbook can have the pages in some order, but then have an index in the back for all the terms. As you can see, the data is precomputed/sorted and stored in a separate area.

When you apply a function to the column and try to match/filter based on the output, the index is no longer useful. Let's take a look at our book example again, and say that the function we're applying is the reverse of the term (so reverse('integral') becomes 'largetni'). You won't find this value in the index, so you have to take all the terms, put them through the function, and only then compare. All at query time. Originally we could skip search for i, then in, then int and so on, making it easy to find the term so the function made everything much slower.

If you query using this function often, you could make an index with reverse(term) ahead of time to speed up look ups. But without doing so explicitly, it will always be slow.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download