MurifoX MurifoX - 16 days ago 4
SQL Question

Performance of querying for a string that starts and ends with something

Are there any performance difference in querying a database for some character column starting with something and ending with something?

SELECT * FROM table WHERE column like '%something'
SELECT * FROM table WHERE column like 'something%'


Any downsides to choose one approach instead of another? Disregarding the user need for matching the start or end of a word.

The way it is implemented maybe the algorithms differ in some way?

Answer

The SQL optimizer will look for an index on column in the second example, and will narrow it down to the records beginning with the characters before the wildcard. In the first example, it can't narrow it down, so you'll like scan either the index or table (depending on index structure).

Which SQL product are you using?