My auto-optimise check code spotted that this was not optimal. I don't
have an index on idValue_5, but I do on the idUnitName. Am I right in
thinking that the idUnitName WILL be optimised, and it will then step
through the results of that for the Between match? If so, then it is
pretty optimal and I'll ignore it. If the name index isn't used, how can I
make it so? Thanks!

/Matthew Jones/

==========================================================================
======
SQL statement (Executed with 4.25 Build 4)
==========================================================================
======

SELECT

idUnitID, idUnitName, idActive,
idVALUE_1,
idVALUE_2,
idVALUE_3,
idVALUE_4

,
id_VALUE_5
FROM InputData
WHERE (id_VALUE_5 BETWEEN 2 AND 3 )

AND (idUnitName = ?)
ORDER BY id_VALUE_5

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

InputData (InputData) table opened shared, has 12 rows

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

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-
sensitive
temporary index:

id_VALUE_5 ASC

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

The expression:

id_VALUE_5 BETWEEN 2 AND 3 AND idUnitName = ?

is UN-OPTIMIZED and will be applied to each candidate row in the InputData
table (InputData) as the result set is generated

==========================================================================
======
>>>>> 2 rows affected in 0 seconds
==========================================================================
======
'

Matthew,

<< My auto-optimise check code spotted that this was not optimal. I don't
have an index on idValue_5, but I do on the idUnitName. Am I right in
thinking that the idUnitName WILL be optimised, and it will then step
through the results of that for the Between match? If so, then it is pretty
optimal and I'll ignore it. If the name index isn't used, how can I make it
so? Thanks! >>

The query plan indicates that no index will be used for any condition of the
WHERE clause.  Is idUnitName a string column ?   If so, is the index on it
case-sensitive ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Okay, that made me go back and look. Turns out my code has a "Delphi"
style creator, but when the data is imported I use SQL code to drop it and
create a new one, and that one wasn't creating the idUnitName index. This
then makes it better! Many thanks for your help - just knowing it wasn't
right is enough to go find the real problem.

/Matthew Jones/