Re: Improving efficiency on char column searches

----- Original Message ----- > From: Dave Thacker <ids@iiug.org> > At: 5/10 11:57:01 > > We have several text searches on name or company name fields that are > getting response time complaints. The typical query looks like this: > "SELECT * FROM my_table > WHERE first_name matches "*John*" AND > > last_name matches"*Doe*"; > > The asterisks are supposed to help deliver the desired name even if the > end user omits characters. Of course this forces us into a sequential > scan and ignores the indexes. I have two questions > > The fields are defined char(30). If I define them as varchar I *may* be > able to get a few more rows on a page. Other than that, is there any > efficiency to be gained by changing them to varchar? > > Are there any other methods of defining the query that would both allow > wildcards and allow me to take advantage of an index on the column? > > TIA > > Dave Thacker > Use directives: SELECT {+INDEX(my_table idx_first_name, idx_last_name)} * FROM my_table WHERE first_name matches "*John*" AND last_name matches"*Doe*"; Best regards, -- Mladen Jovanovski Phone: +389 2 244 1140 Mobile: +389 75 400 309 COSMOFON - Mobile Telecommunications Services - A.D. Skopje _______________________________________________________________ This e-mail (including any attachments) is confidential and may be protected by legal privilege. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete your copy from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. COSMOFON A.D. Skopje shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system.

----- Original Message ----- From: "Mladen Jova...." <mladen.jovanovski@cosmofon.com.mk> To: <ids@iiug.org> Sent: Thursday, May 11, 2006 5:41 AM Subject: Re: Improving efficiency on char column searches [6691] > > ----- Original Message ----- >> From: Dave Thacker <ids@iiug.org> >> At: 5/10 11:57:01 >> >> We have several text searches on name or company name fields that are >> getting response time complaints. The typical query looks like this: >> "SELECT * FROM my_table >> WHERE first_name matches "*John*" AND >> >> last_name matches"*Doe*"; >> >> The asterisks are supposed to help deliver the desired name even if the >> end user omits characters. Of course this forces us into a sequential >> scan and ignores the indexes. I have two questions >> >> The fields are defined char(30). If I define them as varchar I *may* be >> able to get a few more rows on a page. Other than that, is there any >> efficiency to be gained by changing them to varchar? >> >> Are there any other methods of defining the query that would both allow >> wildcards and allow me to take advantage of an index on the column? >> >> TIA >> >> Dave Thacker >> > > Use directives: > > SELECT {+INDEX(my_table idx_first_name, idx_last_name)} * > FROM my_table > WHERE first_name matches "*John*" > AND last_name matches"*Doe*"; > > Best regards, > > -- > Mladen Jovanovski Hmmm. Maybe I am missing something but I don't see how using an index will help a full table scan - in fact it will probably be slower. If one were to use directives then I would recommend the avoid_index directive. Ben

> Use directives: > > SELECT {+INDEX(my_table idx_first_name, idx_last_name)} * > FROM my_table > WHERE first_name matches "*John*" > AND last_name matches"*Doe*"; > > Best regards, > > -- > Mladen Jovanovski > That's clever, but it would seem to be a drag to use the index in such a case as we would have to read every record to satisfy the leading wildcard. Zev Berezin