I think I'm missing the point, but in that case

SELECT first_name, last_name, phone_number
FROM employees

Why the index on last_name (assuming there is an index of course) won't be working ? I mean why the optimizer is not able to parse all the b-tree applying the conversion on each element ?

I agree, it's not a clean way but...

asked Feb 22 '14 at 17:40

stef's gravatar image


2 Answers:

Your understanding is very correct.

EDIT after re-reading your question, I'm not sure if your understanding is correct. ORDER BY LAST_NAME might yield another order than ORDER BY BLACKBOX(LAST_NAME). That's why the B-Tree cannot be used! The database could do an full index scan, however. That's what I assumed as your understanding in the first place.

That's the reason I'm usually using cryptic phrases like this:

LIKE expressions starting with a wildcard cannot use an index to locate the matching entries.

(this is shown at the solution page of the 3-minute test). By "locate the matching entries" I meant "using the b-tree". I guess I should phrase that one more carefully.

LIKE filters can only use the characters before the first wildcard during tree traversal.

Only the part before the first wildcard serves as an access predicate. The remaining characters do not narrow the scanned index range—non-matching entries are just left out of the result.

both quotes from here: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

That's also what is in SQL Performance Explained, there I really took greatest care to phrase it correctly.

Otherwise, if it would use the pattern 'TERM%', it could use the index very efficiently.

This is from the quiz result analysis. I don't say "it cannot use the index" but I say without leading wild-card it would be very efficiently using it ;)

If I missed the cryptic wording to leave the backdoor open, please let me know where. Yours is from here: http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search will check that later one. Might be able to improve it. On the other hand, it is pretty much at the beginning of the book, at that stage it might be more important to scare people off than saying "but the db might be able to use the index anyway".

Often people think if the see any index access in the execution plan, everything is fine. Using an index and efficiently using an index are just two very different things.

Generally, I only consider it proper index usage if the database can take advantage from the index order. Because keeping the index in order is what causes most maintenance overhead. If you don't take advantage of that, I don't think it is proper index usage. Then it is just a smaller copy of the table — which can also improve performance, of course, but I refer to it as an "act of desperation" in lack of any better option.

Do you see my point?

answered Feb 22 '14 at 20:09

Markus%20Winand's gravatar image

Markus Winand ♦♦

edited Feb 22 '14 at 20:23

Hi Markus,

Yes I get the point...

And thanks again for sharing your knowledge.

answered Feb 23 '14 at 12:09

stef's gravatar image