INFORMIX SERVER / 5725A3900 / E10 - 

When doing a "select first n" query using a lvarchar index and
like or matches compariosn.
the query will take a long time when n is greater then the
number of results returns

in this example:
select  search_name, regcode from companies where search_name
like 'CAA%' order by search_name asc
we will get 5 results


select first 5 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc
will return quick:
select first 5 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc

Estimated Cost: 170
Estimated # of Rows Returned: 200000

  1) informix.companies: INDEX PATH

        Filters: informix.companies.search_name LIKE 'CAA%'

    (1) Index Name: informix.idx__companies__search_name
        Index Keys: search_name   (Serial, fragments: ALL)

UDRs in query:
--------------
    UDR id  :   -722
    UDR name:   compare

Query statistics:
-----------------

The final cost of the plan is reduced because of the FIRST n
specification in
 the query.

 Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                companies

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t1     5          200000    34709      00:00.70   170



select first 10 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc

QUERY: (OPTIMIZATION TIMESTAMP: 07-17-2019 15:09:36)
------
select first 10 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc

Estimated Cost: 340
Estimated # of Rows Returned: 200000

  1) informix.companies: INDEX PATH

        Filters: informix.companies.search_name LIKE 'CAA%'

    (1) Index Name: informix.idx__companies__search_name
        Index Keys: search_name   (Serial, fragments: ALL)

UDRs in query:
--------------
    UDR id  :   -722
    UDR name:   compare

Query statistics:
-----------------

The final cost of the plan is reduced because of the FIRST n
specification in
 the query.

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                companies

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t1     5          200000    1000000    00:28.29   341




If we use > and < syntax, it will be fast again, as index
filters will be applied.

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS 14.10.xC1.                                      *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* When doing a "select first n" query using a lvarchar index   *
* and                                                          *
* like or matches comparison,                                  *
* the query will take a long time when n is greater then the   *
* number of results returned.                                  *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

12.08.2019
19.09.2019
19.09.2019

14.10.xC2