Re^2: database sorter script

Like is evil. They do full table scans since a db can't do a function index against it. If there are any databases thatcan do function indexes against "like", I'd love to know.

You are...er.. the OP would be better off, if he were using a formal DB, to create a column called indexed letter or something, that contains only the first letter, and select off of that.

----
Give me strength for today.. I will not talk it away..
Just for a moment.. It will burn through the clouds.. and shine down on me.

Comment on Re^2: database sorter script

Replies are listed 'Best First'.
Re^3: database sorter script by runrig (Abbot) on Dec 06, 2004 at 18:14 UTC
Like is evil. They do full table scans since a db can't do a function index against it.If there are any databases that can do function indexes against "like", I'd love to know. It depends on the database, and your query. If the query begins with a constant string (e.g. `where column like 'ABC%'`) then the database can use an index on the column. If it begins with a wildcard (e.g. `where column like '%ABC'`) then an index on the column can not be used. If your database determines the query plan at 'prepare' time, then it will probably not use an index on a query with placeholders such as `'where column like ?'` since it doesn't know what kind of arguments you'll feed it at 'execute' time.	[reply] [d/l] [select]
Re^4: database sorter script by exussum0 (Vicar) on Dec 06, 2004 at 18:20 UTC
It depends on how the indexing works. Oracle is typically smart enough for it if you use the default index scheme. But mind you, as you brought up, it's not always portable to do what you suggest. Some db's aren't savy like that. Update in italics. ---- Give me strength for today.. I will not talk it away.. Just for a moment.. It will burn through the clouds.. and shine down on me.	[reply]
Re^5: database sorter script by tachyon (Chancellor) on Dec 07, 2004 at 00:21 UTC
LIKE is not inherently evil. It is a tool that has a place but does have the potential for abuse. This is the same for all tools. Nothing is fool proof, fools are simply too ingenious ;-) Anyway MySQL will also use an index for LIKE 'foo%' if there is an index available. It only table scans if you have an initial wildcard LIKE '%foo' or of course if there is no index. cheers tachyon	[reply]