performance of LIKE clause
Hi,
I need a sanity check for some SQL performance I'm seeing on my machine.
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 a beast of an HP R-class (dual Risc CPU, 1Gb
memory, 16Gb disk, 1 user (lucky me), HP-UX 11.0, IDS 7.31)
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
I can't seem to get SET EXPLAIN ON to work within dbaccess error is:
534: Cannot open EXPLAIN output file.
1: Not owner
I'm now looking at UPDATE STATISTICS HIGH etc on some of the rows but the
query seems to do a sequential scan for a LIKE search (this notion based
purely on the hard disk rattle ...) no matter what indexes are applied to
the table.
Does this seem like normal behaviour? what are other folk seeing on a
character search with a million row table. I'm hoping it just isn't always
this slow.
Chris
Further to my last post, got SQLEXPLAIN switched on and my ears didn't fail
me. Can anyone tell me why the engine is not examining the index I created
on lastname_lc ??
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%'
Since I haven't seen the schemas of the tables in question . . . I'll
guess that update stats hasn't been run on these tables. If if ran
'correctly' with only 100 rows, then my guess is that the sequential
scans were quick enough as the tables were relatively small. The
optimizer needs enough information about the indices so that if can make
an intelligent decision about how it needs to retreive the data. Right
now, it's deciding that a sequential scan is the best way.
John Carlson
Informix DBA
WHSmith USA
Chris McKay wrote:
>
> Further to my last post, got SQLEXPLAIN switched on and my ears didn't fail
> me. Can anyone tell me why the engine is not examining the index I created
> on lastname_lc ??
>
> 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%'
Chris,
Do you have a combination index on your order by criteria, lastname_lc,
firstname_lc. We had a couple of instances where our queries were performing
extremely poorly until we put an index on our order by criteria, which then
returned data almost instantly.
Hope that helps,
--Steven
Chris McKay wrote:
> Hi,
>
> I need a sanity check for some SQL performance I'm seeing on my machine.
>
> 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 a beast of an HP R-class (dual Risc CPU, 1Gb
> memory, 16Gb disk, 1 user (lucky me), HP-UX 11.0, IDS 7.31)
>
> 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
>
> I can't seem to get SET EXPLAIN ON to work within dbaccess error is:
>
> 534: Cannot open EXPLAIN output file.
> 1: Not owner>
> I'm now looking at UPDATE STATISTICS HIGH etc on some of the rows but the
> query seems to do a sequential scan for a LIKE search (this notion based
> purely on the hard disk rattle ...) no matter what indexes are applied to
> the table.
>
> Does this seem like normal behaviour? what are other folk seeing on a
> character search with a million row table. I'm hoping it just isn't always
> this slow.
>
> Chris
--
-----------------------------------------------------
Steven Mastandrea stevem@cstech.com
Systems Designer 847.397.7300
CSTech, Inc. Schaumburg, IL