Re: Help! my install of ONLINE 4.00 is 30% slower than Standard Engine
>From: uunet!cpqhou.compaq.com!thomasr (Thomas Rush)
>Date: 10 Apr 92 14:18:13 GMT
>Message-Id: <1992Apr10.141813.26235@cpqhou.compaq.com>
>Subject: Re: Help! my install of ONLINE 4.00 is 30% slower than Standard Engine
>Organization: Compaq Computer Corporation
>X-Informix-List-Id: <news.989>
>
>In article <1992Mar26.204539.7416@informix.com> cortesi@informix.com writes:
>>
>>Regarding your choice of query, "where description matches '*MEM*'"
>>will always use a sequential scan. MATCHES (or LIKE) with a leading
>>wildcard can't use an index.
>>
>> Dave Cortesi
>
>Hi, Dave!
>
> While it may be true to say that MATCHES with a leading
>wildcard _doesn't_ use an index, isn't it possible that using the
>index might be faster:
>
> since the index will have only this column, amount of data
> to be searched is only the column we're interested in,
> and,
> it is possible that finding a hit may allow inteligent use
> of Informix's compression scheme to guarantee that the next
> _n_ rows are hits also?
>
> Just wondering if anyone's actually done any looking into
>this, or if it is one of the things that everybody "just _knows_."
>
>thomas rush uunet!cpqhou!thomasr
>compaq computer corporation their employee,
>deep in the hearth of texas not their opinions.
Below is some output from SET EXPLAIN. I think you will see that the
optimiser (Version 4.10.UC2) does use index path when the only data
required is the indexed column.
SCHEMA:
CREATE TABLE Junk(J01 SERIAL NOT NULL, J02 CHAR(20) NOT NULL);
CREATE UNIQUE INDEX Pk_junk ON Junk(J01);
CREATE UNIQUE INDEX Ak_junk ON Junk(J02);
QUERY:
------
select * from junk where j02 matches "*c*"
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) johnl.junk: SEQUENTIAL SCAN
Filters: johnl.junk.j02 MATCHES '*c*'
QUERY:
------
select j02 from junk where j02 matches "*c*"
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) johnl.junk: INDEX PATH
Filters: johnl.junk.j02 MATCHES '*c*'
(1) Index Keys: j02 (Key-Only)
Yours sincerely,
Jonathan Leffler (johnl@obelix.informix.com)