Hi

I have a select than use two fields, this two fields are in an index of
the table. But the select only use the first field of the index.

The plan indicates that the WHERE is partially optimized; but the index
is the two fields (Codi_caixa + Numero_tiquet).

Why the WHERE is not optimized ?

Regards
Robert



================================================================================
SQL statement (Executed with 4.21 Build 10)
================================================================================

SELECT *
FROM TIQ_LIN
WHERE "Codi_caixa" = '01'
AND "Numero_tiquet" = '00100000'

Tables Involved
---------------

TIQ_LIN (TIQ_LIN) table opened shared, has 500502 rows

Result Set Generation
---------------------

Result set will be live

Result set will consist of one or more rows

Result set will be ordered by the primary index for the table TIQ_LIN

WHERE Clause Execution
----------------------

The expression:

"Codi_caixa" = '01' AND "Numero_tiquet" = '00100000'

is PARTIALLY-OPTIMIZED, covers 500435 rows or index keys, costs
189714908 bytes,
 and will be applied to the TIQ_LIN table (TIQ_LIN) before any joins

================================================================================
>>>>> 1 rows affected in 13,484 seconds
================================================================================

Assoes


DBISAM only uses the first part of any compound index (apart from ordering) if you want to fully optimise the query you'll have to create a second index on Numero_tiquet

Roy Lambert

> DBISAM only uses the first part of any compound index (apart from ordering) if you want to fully optimise the query you'll have to create a second index on Numero_tiquet
>

Thank you Roy, this works perfect.

Regards
Robert