Improving efficiency on char column searches
Dave Thacker asked how to speed up name/company searches using MATCHES '*John*' style patterns on char(30) columns, which force sequential scans, and whether switching to varchar would help. Replies agreed leading wildcards can't use a B-tree index (only trailing-wildcard patterns like 'John*' can), so options are: refactor into a narrower search table, cache/fragment/parallelize or use solid-state disk, use LIKE rather than MATCHES unless regex is needed, check SET EXPLAIN, add a text-search datablade (Excalibur, or the third-party SpeedIndex, which one poster found faster and more stable), or index a soundex-style derived value to narrow candidates. No single accepted fix was reported by the original poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
Dave Thacker schrieb: > 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 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > It all depens on the details. How large are those tables? Can you refactor them (like getting those CHAR(30) & a primary key into an extra table, search thee & use the PK to access the whole row) Is it possible to kepp them cached 100% (= in memory) Is is possibke to use parallelism via fragmentation Can you install Solid State Disks for this type of tables when you do the seq scans? Solid State Disks are memory, connected via Fibre Channel (for instance) or SCSI 320 AND can saturate such interconnections with ease, but cacheing in the buffer pool is faster. Each and every action desribed above would be an improvement of some sort. How much depends on the details, though. dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe
Version and platform always help! If you are using 9.xx+ you can get the Excaliber text indexing datablade. Check it out. Art S. Kagel ----- 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 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
On 5/10/06, Dave Thacker <dthacker@omnicorporate.com> wrote: > > 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*"; > Hi Dave, First of all, it is my understanding that MATCHES uses a regexp match function when comparing the column with a pattern (at least the fine manual shows a larger pattern space for MATCHES, comparing with LIKE). It should be advisable to use LIKE until you really need the regular expression matching. Second, even if the columns (first_name, last_name) are indexed, using a matching pattern with both ends patterned will yield in a full index scan - depending on the size of the index (rows * (key size + rowid)). If it is possible, use the SET EXPLAIN ON to get the execution plan. If it is possible to use the pattern "XXX%" / "XXX*" the index scan will use a lower filter, starting the scan deep into the index. Hope it helps, Bogdan.
We are using an alternative to Excalibur called SpeedIndex by SpeedOfMind (a Danish company). SpeedIndex is an external search engine integrated with Informix as a data blade. (We use it with 9.40 on Solaris). We tried Excalibur a couple of years ago but found it too slow and buggy. With our size of the database (where we need to have text search index for a number of columns) we could not even create the index, Excalibur crashed. SpeedIndex is extremely efficient and handles large mount amounts of data. The drawback is that it is completely integrated as the search engine itself is a separate process. Regards Rolf Wasteson ------- Original Message ------- Subject: Re: Improving efficiency on char column searches [6686] From: "ART KAGEL, ...." (kagel@bloomberg.net) To: ids (ids@iiug.org) Sent: 2006-05-11 00:35 From: kagel@bloomberg.net To: ids@iiug.org Date: Wed, 10 May 2006 18:35:38 -0400 Reply-To: ids@iiug.org Subject: Re: Improving efficiency on char column searches [6686] Message-Id: <20060510223538.329969EFD@perform.iiug.org> Version and platform always help! If you are using 9.xx+ you can get the Excaliber text indexing datablade. Check it out. Art S. Kagel ----- 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 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
How many records do you have. I think you have a lot for informix to give slow responses for the query you propose. I think that you cannot get a better response if you do a search like _matches '*<some-string>*'_. The problem is that in this type of searches the optimizer won't use the indexes you have because an index is not designed to quickly locate intermediate data in a string. Other would be the case if you where looking for _matches '<some-string>*'_. That surely will use an index if you have it. '*<some-string>*' has no more option than do a sequential scan. The database cannot do any better than that. And the way to reduce search times are as Mr. Kofler said, use fragmentation, paralelism, caching, etc. You can try some kind of functional index, but I can't think of a good function to index substrings. That could end in something very sophisticated like a datablade for searching text. I know informix has one but I don't know how to use it. J. -----Original Message----- From: "Dave Thacker" <dthacker@omnicorporate.com> To: ids@iiug.org Date: Wed, 10 May 2006 11:54:00 -0400 (EDT) Subject: Improving efficiency on char column searches [6683] 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 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com
I would use a soundex style approach. Something that you CAN index that returns a subset of records that you can then work through. This is a technique I've seen used a lot in name list management, where you've got 100 million names and 200K updates going against it and all you have to work with is the name. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Jean Sagi Sent: Thursday, May 11, 2006 2:06 PM To: ids@iiug.org Subject: Re: Improving efficiency on char column searches [6696] How many records do you have. I think you have a lot for informix to give slow responses for the query you propose. I think that you cannot get a better response if you do a search like _matches '*<some-string>*'_. The problem is that in this type of searches the optimizer won't use the indexes you have because an index is not designed to quickly locate intermediate data in a string. Other would be the case if you where looking for _matches '<some-string>*'_. That surely will use an index if you have it. '*<some-string>*' has no more option than do a sequential scan. The database cannot do any better than that. And the way to reduce search times are as Mr. Kofler said, use fragmentation, paralelism, caching, etc. You can try some kind of functional index, but I can't think of a good function to index substrings. That could end in something very sophisticated like a datablade for searching text. I know informix has one but I don't know how to use it. J. -----Original Message----- From: "Dave Thacker" <dthacker@omnicorporate.com> To: ids@iiug.org Date: Wed, 10 May 2006 11:54:00 -0400 (EDT) Subject: Improving efficiency on char column searches [6683] 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 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Interesting. J. Jack Parker escribió: > I would use a soundex style approach. Something that you CAN index that > returns a subset of records that you can then work through. This is a > technique I've seen used a lot in name list management, where you've got 100 > million names and 200K updates going against it and all you have to work > with is the name. > > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > Jean Sagi > Sent: Thursday, May 11, 2006 2:06 PM > To: ids@iiug.org > Subject: Re: Improving efficiency on char column searches [6696] > > How many records do you have. I think you have a lot for informix to give > slow > responses for the query you propose. > > I think that you cannot get a better response if you do a search like > _matches > '*<some-string>*'_. > > The problem is that in this type of searches the optimizer won't use the > indexes you have because an index is not designed to quickly locate > intermediate data in a string. > > Other would be the case if you where looking for _matches '<some-string>*'_. > That surely will use an index if you have it. > > '*<some-string>*' has no more option than do a sequential scan. The database > cannot do any better than that. And the way to reduce search times are as > Mr. > Kofler said, use fragmentation, paralelism, caching, etc. > > You can try some kind of functional index, but I can't think of a good > function to index substrings. > > That could end in something very sophisticated like a datablade for > searching > text. I know informix has one but I don't know how to use it. > > J. > > -----Original Message----- > From: "Dave Thacker" <dthacker@omnicorporate.com> > To: ids@iiug.org > Date: Wed, 10 May 2006 11:54:00 -0400 (EDT) > Subject: Improving efficiency on char column searches [6683] > > 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 > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > Jean Sagi > jeansagi@myrealbox.com > jeansagi@gmail.com > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >