it's not really a problem of fetch time because i alway put 'First 10 skip 0' so i fetch only 10 records max

Query time: 0ms
Execute: 483 ms
Avg fetch time: 48 ms

the difference in speed between an index on fieldA and fieldB is :

SELECT
FIRST 1 *
FROM
TableX x
WHERE
x.FieldA > 1 and x.FieldA < 10000000 and
x.FieldB > 5 and x.FieldB < 6
PLAN (Tablex INDEX (FieldA))

execution time: 7.44 Seconds

SELECT
FIRST 1 *
FROM
TableX x
WHERE
x.FieldA > 1 and x.FieldA < 10000000 and
x.FieldB > 5 and x.FieldB < 6
PLAN (Tablex INDEX (FieldA, FiledB))

execution time: 500 ms

SELECT
FIRST 1 *
FROM
TableX x
WHERE
x.FieldA > 1 and x.FieldA < 10000000 and
x.FieldB > 5 and x.FieldB < 6
PLAN (Tablex INDEX (FieldB))

execution time: 0 ms (of course, because the restriction is on field B in this sample)

for the test we use firebird 2.1.2 but i don't thing it's will change in 2.1.3 ?


thanks
stephane

--- In firebird-support@yahoogroups.com, "Arno Brinkman" <fbsupport@...> wrote:
>
> Hi,
>
> > unfortunatly put an index on fieldA and another on fieldB not help ! same speed (or i miss
> > something, i probably write badly the query plan ?)
>
> Could (or may be you already do) also test the difference between the time for the "index builds"
> and "fetch time".
>
> What is the difference in speed between (with an index on FieldA and an index on FieldB)
>
> SELECT
> FIRST 1 *
> FROM
> TableX x
> WHERE
> x.FieldA > 1 and x.FieldA < 10000000 and
> x.FieldB > 5 and x.FieldB < 6
>
> and
>
> SELECT
> *
> FROM
> TableX x
> WHERE
> x.FieldA > 1 and x.FieldA < 10000000 and
> x.FieldB > 5 and x.FieldB < 6
>
> Do you always need to fetch all rows?
>
> Which Firebird version are you using?
>
> Regards,
> Arno Brinkman
> ABVisie
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
> General database development support:
> http://www.databasedevelopmentforum.com
>
> Firebird open source database (based on IB-OE) with many SQL-99 features :
> http://www.firebirdsql.org
> http://www.firebirdsql.info
> http://www.fingerbird.de/
> http://www.comunidade-firebird.org/
>
> Support list for Firebird and Interbase users :
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep :
> news://newsgroups.firebirdsql.info
>