SELECT by string prefix using an index

Question

You have a column foo, of some string type, with a index on that column. You want to SELECT from the table WHERE the foo column has the prefix 'pre'. Obviously, the index should be able to help here.

Here is the most obvious way to search by prefix:

SELECT * FROM tab WHERE foo LIKE 'pre%';

Unfortunately, this does not get optimized to use the index (in Oracle or Postgres, at least).

The following, however, does work:

SELECT * FROM tab WHERE 'pre' <= foo AND foo < 'prf';

But are there better ways to accomplish this, or are there ways of making the above more elegant? In particular:

I need a function from 'pre' to 'prf', but this has to work for any underlying collation. Also, it's more complicated than above, because if searching for e.g. 'prz' then the upper bound would have to be 'psa', and so on.
Can I abstract this into a stored function/procedure and still hit the index? So I could write something like ... WHERE prefix('pre', foo);?

Answers for all DBMSes appreciated.

What? Unfortunately, this 'this does get optimized to use the index'? Was so bad about it? Of course, a 'begins with' search uses an index, if possible. Thanks god it does. .... Did I misunderstand you? — alzaimar
– alzaimar, Commented Mar 23, 2013 at 22:20
Oh and do you mean there is a "begins with" construct in SQL? I can't find anything about that. — jameshfisher
– jameshfisher, Commented Mar 23, 2013 at 22:36
It's very strange that LIKE 'pre%' can't use an index in those DBMSes. It is sargable in SQL Server, at least. — ErikE
– ErikE, Commented Mar 23, 2013 at 23:15

Gordon Linoff · Accepted Answer · 2013-03-23 22:50:37Z

0

The database is quite important here. It so happens that SQL Server does this optimization for like.

One way is to do something like this:

where foo >= 'pre' and foo <= 'pre+'~'

'~' has the largest 7-bit ASCII value of a printable character, so it is basically bigger than anything else. This however, may be a problem if you are using wide characters or a non-standard character set.

You cannot abstract this into a function, because use of a function generally precludes the use of indexes. If you are always looking at the first three characters, then in Oracle you can create an index on those three characters (something called a "function-based index").

answered Mar 23, 2013 at 22:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jameshfisher Over a year ago

I don't think foo <= 'pre~' works, because it cuts out values like 'pre~fix'. If using a <= comparison, the value would have to be pre~~~~~~~~~... carrying on forever. Use of functions: I think you're right. Perhaps macros in SQL would be a nice thing, but that's pie-in-the-sky.

alzaimar · Accepted Answer · 2013-03-23 23:08:53Z

-3

How about

select * from tab where foo between 'pre' and 'prf' and foo != 'prf'

this enables the index same way. The RDBMS must be pretty dumb not to use an index for that.

answered Mar 23, 2013 at 23:08

alzaimar

4,6321 gold badge22 silver badges31 bronze badges

4 Comments

ErikE Over a year ago

You use inclusive end range, then have to specify another condition to make the end exclusive? Why not foo >= 'pre' AND foo < 'prf'?

alzaimar Over a year ago

Nice question. I haven't tried it, but from my guts it seems as if between is better. Hmm... Am I too much in belief where I should be more into proof?

jameshfisher Over a year ago

Is x BETWEEN l AND h not just sugar for l <= x AND x <= h?

ErikE Over a year ago

There's no way that BETWEEN can be better. As @eegg said, all it is is syntactic sugar expanded to two <= expressions, just like IN expands to OR expressions. You wouldn't say 'pre' <= foo AND ' foo <= 'pre' AND foo <> 'pre', would you? So it's "wrong" to do it with BETWEEN. Perhaps in the future more testing/proof is better than "instinct". :)

Collectives™ on Stack Overflow

SELECT by string prefix using an index

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related