WHERE LIKE performance

Apologies if this is a repeat post, my ISP's news server is playing up and I had a little difficulty coming to grips with deja news... I need a sanity check for some SQL performance I'm seeing on my very grunty 2 CPU, 1Gb memory, 1 user (lucky me!) HP R-class. My actual query is as follows: SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROMUSERS WHERE lastname_lc LIKE 'm%' AND (firstname_lc LIKE 'chris%' OR altfirstname_lc LIKE 'cmc%') ORDER BY lastname_lc, firstname_lc; which runs just dandy when there are 100 rows in the database. I've now loaded a million rows into the sucker and needless to say it runs like a dog - 1min 30 sec even on the beast. So I've been fooling around trying to optimise the query. I have not configured Informix to anything other than the default settings so that may be the first problem. But when I run this query SELECT FIRST 100 lastname_lc FROM USERS WHERE lastname_lc LIKE 'm%' ; It takes 1minute 30 as well! These are some of my indexes: idx_firstname informix dupls No firstname_lc idx_lastname informix dupls No lastname_lc idx_altfirstname informix dupls No altfirstname_lc Part of the Users schema: firstName_lc VARCHAR(30), lastName_lc VARCHAR(30), altFirstName_lc VARCHAR(30) This is the SQLEXPLAIN output QUERY: ------ SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROMUSERS WHERE lastname_lc LIKE 'cmc%' AND (firstname_lc LIKE 'chris%' OR altfirstname_lc LIKE 'mckay%') ORDER BY lastname_lc, firstname_lc Estimated Cost: 140053 Estimated # of Rows Returned: 72002 Temporary Files Required For: Order By 1) informix.users: SEQUENTIAL SCAN Filters: (informix.users.lastname_lc LIKE 'cmc%' AND (informix.users.firstname_lc LIKE 'chris%' OR informix.users.altfirstname_lc LIKE 'mckay%' ) ) QUERY: ------ SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROMUSERS WHERE lastname_lc LIKE 'cmc%' Estimated Cost: 101435 Estimated # of Rows Returned: 200004 1) informix.users: SEQUENTIAL SCAN Filters: informix.users.lastname_lc LIKE 'cmc%' QUERY: ------ SELECT firstname, lastname, firstname_lc, lastname_lc FROM USERS WHERE lastname_lc LIKE 'cmc%' Estimated Cost: 101435 Estimated # of Rows Returned: 200004 1) informix.users: SEQUENTIAL SCAN Filters: informix.users.lastname_lc LIKE 'cmc%' So the optimizer is not using the index at all, what the heck is going on? or is it because the esitmated number of rows is so high that the optimiser says you'll have to run through a bunch of rows anyway, do a seq scan to get it over with? I'm now looking at UPDATE STATISTICS HIGH etc on some of the columns and will drop and recreate the indexes. Any hints welcome! Chris Sent via Deja.com http://www.deja.com/ Before you buy.

In article <7uoeae$vur$1@nnrp1.deja.com>, chris_mckay@hotmail.com writes >Apologies if this is a repeat post, my ISP's news server is playing >up and I had a little difficulty coming to grips with deja news... > >I need a sanity check for some SQL performance I'm seeing on my very >grunty 2 CPU, 1Gb memory, 1 user (lucky me!) HP R-class. > >My actual query is as follows: > >SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROM>USERS >WHERE lastname_lc LIKE 'm%' AND (firstname_lc LIKE 'chris%' OR >altfirstname_lc LIKE 'cmc%') >ORDER BY lastname_lc, firstname_lc; > >which runs just dandy when there are 100 rows in the database. I've now >loaded a million rows into the sucker and needless to say it runs like a >dog - 1min 30 sec even on the beast. > >So I've been fooling around trying to optimise the query. I have not >configured Informix to anything other than the default settings so that >may be the first problem. > >But when I run this query > >SELECT FIRST 100 lastname_lc FROM USERS >WHERE lastname_lc LIKE 'm%' ;> >It takes 1minute 30 as well! > >These are some of my indexes: >idx_firstname informix dupls No firstname_lc >idx_lastname informix dupls No lastname_lc >idx_altfirstname informix dupls No altfirstname_lc > >Part of the Users schema: >firstName_lc VARCHAR(30), >lastName_lc VARCHAR(30), >altFirstName_lc VARCHAR(30) > >This is the SQLEXPLAIN output >QUERY: >------ >SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROM>USERS >WHERE lastname_lc LIKE 'cmc%' AND (firstname_lc LIKE 'chris%' OR >altfirstname_lc LIKE 'mckay%') >ORDER BY lastname_lc, firstname_lc > >Estimated Cost: 140053 >Estimated # of Rows Returned: 72002 >Temporary Files Required For: Order By > Could be that the order by affects things. I've seen this recently where removing the order by allow SE 6.x to use the index. Try select..into temp t1 with no log; select...from t1 order by lastname_lc, firstname_lc Ah, got it, OR forces a sequential scan. Try can you do a union with SELECT FIRST?? Generally select ...where (col1 = 1) or (col2=2) is the same as select ... where col1=1 union select ... where col2=2 order by 2,1 ^^^ NOTE YOU HAVE TO ORDER BY COLUMN NOS NOT COLUMN NAMES! >1) informix.users: SEQUENTIAL SCAN > > Filters: (informix.users.lastname_lc LIKE 'cmc%' AND >(informix.users.firstname_lc LIKE 'chris%' OR >informix.users.altfirstname_lc >LIKE 'mckay%' ) ) > >QUERY: >------ >SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROM>USERS >WHERE lastname_lc LIKE 'cmc%' > >Estimated Cost: 101435 >Estimated # of Rows Returned: 200004 > >1) informix.users: SEQUENTIAL SCAN > > Filters: informix.users.lastname_lc LIKE 'cmc%' > >QUERY: >------ >SELECT firstname, lastname, firstname_lc, lastname_lc FROM USERS >WHERE lastname_lc LIKE 'cmc%'> >Estimated Cost: 101435 >Estimated # of Rows Returned: 200004 > >1) informix.users: SEQUENTIAL SCAN > > Filters: informix.users.lastname_lc LIKE 'cmc%' > >So the optimizer is not using the index at all, what the heck is going >on? > >or is it because the esitmated number of rows is so high that the >optimiser says you'll have to run through a bunch of rows anyway, do a >seq scan to get it over with? > Possibly. Do update stats first. >I'm now looking at UPDATE STATISTICS HIGH etc on some of the columns and >will drop and recreate the indexes. Any hints welcome! > >Chris > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams

<chris_mckay@hotmail.com> wrote in message news:7uoeae$vur$1@nnrp1.deja.com... > I'm now looking at UPDATE STATISTICS HIGH etc on some of the columns and > will drop and recreate the indexes. Any hints welcome! > For the information of any interested parties UPDATE STATISTICS MEDIUM on the table and UPDATE STATISTICS HIGH of the indexed columns improved the query mentioned from 1 min 30 sec to <=1 sec. Informix has changed a bit from the Turbo days then ...

Your main problem is that the indexes are not being used because the engine has no statistics that it can use to determine the best query plan so it is guessing. Last thing it knew the table had no rows in it so a sequential scan is a nifty idea. Read the Performance Guide for the optimal set of update statistics statements to get usable stats in least time. These recommendations are implemented in my dostats.ec program and in scripts and utilities by others sith varying levels of accuracy and features/flexibility. Dostats.ec is part of the package utils2_ak which you can download from the IIUG Software Repository. Anyway for THIS specific table you need to do: UPDATE STATISTICS MEDIUM FOR TABLE users DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE users (firstname_lc); #Includes LOW UPDATE STATISTICS HIGH FOR TABLE users (lastname_lc); #Includes LOW UPDATE STATISTICS HIGH FOR TABLE users (altfirstname_lc); #Includes LOW Art S. Kagel chris_mckay@hotmail.com wrote: > > Apologies if this is a repeat post, my ISP's news server is playing > up and I had a little difficulty coming to grips with deja news... > > I need a sanity check for some SQL performance I'm seeing on my very > grunty 2 CPU, 1Gb memory, 1 user (lucky me!) HP R-class. > > My actual query is as follows: > > SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROM> USERS > WHERE lastname_lc LIKE 'm%' AND (firstname_lc LIKE 'chris%' OR > altfirstname_lc LIKE 'cmc%') > ORDER BY lastname_lc, firstname_lc; > > which runs just dandy when there are 100 rows in the database. I've now > loaded a million rows into the sucker and needless to say it runs like a > dog - 1min 30 sec even on the beast. > > So I've been fooling around trying to optimise the query. I have not > configured Informix to anything other than the default settings so that > may be the first problem. > > But when I run this query > > SELECT FIRST 100 lastname_lc FROM USERS > WHERE lastname_lc LIKE 'm%' ;> > It takes 1minute 30 as well! > > These are some of my indexes: > idx_firstname informix dupls No firstname_lc > idx_lastname informix dupls No lastname_lc > idx_altfirstname informix dupls No altfirstname_lc > > Part of the Users schema: > firstName_lc VARCHAR(30), > lastName_lc VARCHAR(30), > altFirstName_lc VARCHAR(30) > > This is the SQLEXPLAIN output > QUERY: > ------ > SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROM> USERS > WHERE lastname_lc LIKE 'cmc%' AND (firstname_lc LIKE 'chris%' OR > altfirstname_lc LIKE 'mckay%') > ORDER BY lastname_lc, firstname_lc > > Estimated Cost: 140053 > Estimated # of Rows Returned: 72002 > Temporary Files Required For: Order By > > 1) informix.users: SEQUENTIAL SCAN > > Filters: (informix.users.lastname_lc LIKE 'cmc%' AND > (informix.users.firstname_lc LIKE 'chris%' OR > informix.users.altfirstname_lc > LIKE 'mckay%' ) ) > > QUERY: > ------ > SELECT FIRST 100 firstname, lastname, firstname_lc, lastname_lc FROM> USERS > WHERE lastname_lc LIKE 'cmc%' > > Estimated Cost: 101435 > Estimated # of Rows Returned: 200004 > > 1) informix.users: SEQUENTIAL SCAN > > Filters: informix.users.lastname_lc LIKE 'cmc%' > > QUERY: > ------ > SELECT firstname, lastname, firstname_lc, lastname_lc FROM USERS > WHERE lastname_lc LIKE 'cmc%'> > Estimated Cost: 101435 > Estimated # of Rows Returned: 200004 > > 1) informix.users: SEQUENTIAL SCAN > > Filters: informix.users.lastname_lc LIKE 'cmc%' > > So the optimizer is not using the index at all, what the heck is going > on? > > or is it because the esitmated number of rows is so high that the > optimiser says you'll have to run through a bunch of rows anyway, do a > seq scan to get it over with? > > I'm now looking at UPDATE STATISTICS HIGH etc on some of the columns and > will drop and recreate the indexes. Any hints welcome! > > Chris > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Chris McKay wrote: > > <chris_mckay@hotmail.com> wrote in message > news:7uoeae$vur$1@nnrp1.deja.com... > > > I'm now looking at UPDATE STATISTICS HIGH etc on some of the columns and > > will drop and recreate the indexes. Any hints welcome! > > > For the information of any interested parties UPDATE STATISTICS MEDIUM on > the table and UPDATE STATISTICS HIGH of the indexed columns improved the > query mentioned from 1 min 30 sec to <=1 sec. Beat my guess of <10 secs. :-) > Informix has changed a bit from the Turbo days then ... Yes, the IDS optimizer is generations ahead of the old Turbo optimizer. It is far more intelligent, however, as someone once said, "Intelligence without knowledge is wasted space". The optimizer needs those stats. Art S. Kagel