Re: Forcing use of an index

"Bob P" <bob_pombrio_at_yahoo.com> wrote in message news:1111071090.623398.29830_at_z14g2000cwz.googlegroups.com...
> Frank,
> The input is usally 4-6 bytles long (7339) and can be anywhere in the
> column thus the leading %. I'm looking for a way to speed up the query
> because it is hit several thousand time a day. I was hoping someone
> had solved this problem before and that I could make use of that
> knowledge.
>

The users want their cake and eat it to. One thing you could do is have another table with this ID and the primary key of the table. Don't bother putting indexes on this new table.(just a waste of space) Also make sure the percent used is 99. Now search for the ID in this new table and when you find it look up the complete row in the original table. You will still do a full table scan, but on a much smaller table. (hence faster) You could also cache this smaller table and speed up the query. Of course, you have to keep the table in sync with the other one, a trigger would do it.

If you can't do that then you really don't have any options. It sounds like your constraints are such that an optimal solution is not available. Jim Received on Thu Mar 17 2005 - 09:11:16 CST