Indexes list keywords and other data that direct the user to the location of specific information stored in database tables. A database table can have one or more indexes associated with it. The efficiency of your searches for information stored in your tables can be improved dramatically if your program or query can use indexes and keys to access the information.

Indexes and keys related to your tables are defined in the CONNX Data Dictionary.

To get the most out of the CONNX ODBC driver, and to improve your search results, note the following hypothetical SQL substitutions:

When selecting a range of values:
(COL1 is an indexed column.)

Instead of

select * from <yourtable>
where col1 <= mAXvalue
and col1 >= mINvalue

Use

SELECT * FROM <YOURTABLE>
WHERE COL1 BETWEEN MINVALUE AND MAXVALUE

When selecting a set of values:

Instead of

SELECT * FROM <YOURTABLE>
WHERE COL1 = TESTVALUE1
OR COL1 = TESTVALUE2
OR COL1 = TESTVALUE3

Use

SELECT * FROM YOURTABLE
WHERE COL1 IN (TESTVALUE1, TESTVALUE2, TESTVALUE3)

When joining tables:

Use a constricting WHERE clause in all tables referenced in a join.

Instead of

SELECT * FROM <YOURTABLE1>,< YOURTABLE2>
WHERE YOURTABLE1.COL1 = TESTVALUE
AND YOURTABLE1.COL1 = YOURTABLE2.COL1

Use

SELECT * FROM <YOURTABLE1>, <YOURTABLE2>
WHERE YOURTABLE1.COL1 = TESTVALUE
AND YOURTABLE1.COL1 = YOURTABLE2.COL1
AND YOURTABLE2.COL1 = TESTVALUE