i post below the exact detail of the log of the second query (that is slow):


Query Time
------------------------------------------------
Prepare : 0 ms
Execute : 32 ms
Avg fetch time: 32.00 ms

Memory
------------------------------------------------
Current: 46436312
Max : 110457252
Buffers: 10240

Operations
------------------------------------------------
Reads : 187997
Writes : 0
Fetches: 1086042

Plan:
------------------------------------------------
PLAN (GEO_PLACE INDEX (RDB$PRIMARY24))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
| Table Name | Index | Non-Index | Updated | Deleted | Inserted |
| | reads | reads | | | |
+--------------------------+-------+-----------+---------+---------+----------+
| RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 | 0 |
| RDB$RELATION_CONSTRAINTS| 1 | 0 | 0 | 0 | 0 |
| RDB$PROCEDURES| 0 | 82 | 0 | 0 | 0 |
| GEO_PLACE| 542,1 | 0 | 0 | 0 | 0 |
+--------------------------+-------+-----------+---------+---------+----------+

--- In firebird-support@yahoogroups.com, "svanderclock" <svanderclock@...> wrote:
>
> so :
>
> Select
> First 15
> ID,
> rank
> FROM GEO_PLACE
> WHERE
> ID = 'RU'
>
> geo_place
> indexed read: 4
> non indexed read: 0
>
> rdb$procedure
> indexed read: 0
> non indexed read: 82
>
>
> Select
> First 1
> ID,
> rank
> FROM GEO_PLACE
> WHERE
> ID like 'R%'
>
> geo_place
> indexed read: 542167
> non indexed read: 0
>
> rdb$procedure
> indexed read: 0
> non indexed read: 82
>
>
> why so much indedxed read in the second variante ?? the result is the same...
>
> thanks by advance for you advice !
> stephane
>
> --- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@> wrote:
> >
> > Stephane,
> >
> > > => time taken 2ms
> > > PLAN (GEO_PLACE INDEX (RDB$PRIMARY24))
> >
> >
> > > => time taken 1230ms
> > > PLAN (GEO_PLACE INDEX (RDB$PRIMARY24))
> >
> >
> > > => time taken 80ms
> > > PLAN (GEO_PLACE INDEX (RDB$PRIMARY24))
> > >
> > >
> > > Why with the like clause it's soo long ??
> >
> > The PLAN does not explain all aspect of how the engine is
> > operating/evaluating a condition, it only indicates which index is being
> > used.
> >
> > It is most likely that the LIKE command needed to read more index pages
> > in order to resolve the result.
> >
> > Your 2nd LIKE statement was faster simply because it used the same pages
> > which were loaded the computer memory/cache from the first query, so it
> > ran faster.
> >
> > The full details of the number of disk reads, disk fetches are really
> > required to explain the performance difference.
> >
> >
> > Sean
> >
>