Hi All,

Quick question on indexes...

With a query like this:

SELECT COUNT(*) AS TransCount FROM trans WHERE res_num = 'A4444' OR
orig_res_num = 'A4444'

Am I better off creating ONE index with both the res_num and
orig_res_num columns

OR

2 separate indexes (and these are already separate indexes and they are
referenced by other queries that use the 2 fields separately.

Thanks,

Phil.

Phil


Stick with the two indices. AFAIK in ELevateDB's SQL only the first column will be taken account of for selection. The only time a compound index pays dividends is for ORDER BY clauses.

Roy Lambert [Team Elevate]

> Stick with the two indices. AFAIK in ELevateDB's SQL only the first column will be taken account of for selection. The only time a compound index pays dividends is for ORDER BY clauses.
>
> Roy Lambert [Team Elevate]

Thanks Roy,

That was the part in the manual I wasn't sure about where only the first
index is taken into account for selection.

I'll leave the 2 seperate indexes in place then and remove the index
with both.

Thanks mate,

Phil.

As a side note, not related with this particular case,
there is in fact another case where compound indexes are used.
It's if we use row value constructors, like in this example:

SELECT * FROM Tbl WHERE (LastName, FirstName) = ('Lambert', 'Roy')

--
Fernando Dias
[Team Elevate]

> As a side note, not related with this particular case,
> there is in fact another case where compound indexes are used.
> It's if we use row value constructors, like in this example:
>
> SELECT * FROM Tbl WHERE (LastName, FirstName) = ('Lambert', 'Roy')

Interesting, I'll keep that in mind, thanks Fernando!

Phil,

re:the last post by Fernando Dias.

Check the edb2sql.pdf manual on page 74 which explains this.
I'm assuming this also works on table joins with compound keys, but I haven't checked it out.


Barry

Employee

Column Data Type Index
----------------------------------------------------------------------
LastName VARCHAR(30) Primary Key (both columns are part of the
FirstName VARCHAR(20) Primary Key primary key constraint)

And consider the following SELECT statement:

SELECT *
FROM Employee
WHERE (LastName = 'Smith') and (FirstName = 'John')

Logically you would assume that ElevateDB can use the one index available for the enforcement of the
primary key constraint in order to optimize the entire WHERE clause. Unfortunately, this is not the case,
and instead ElevateDB will only use the index created for the primary key constraint for optimizing the
LastName expression and resort to reading the resultant rows in order to evaluate the FirstName
expression.

However, you can overcome this limitation by using a row value constructor instead of two expressions
combined with the AND operator:

SELECT *
FROM Employee
WHERE (LastName,FirstName) = ('Smith','John')

With the WHERE clause specified using a row value constructor, ElevateDB will be able to use the entire
primary key to optimize the expression.

Barry,

<< I'm assuming this also works on table joins with compound keys, but I haven't checked it out.  >>

Yeah , it does.
As well as in PSM cursors.

--
Fernando Dias
[Team Elevate]

Phil,

<< With a query like this:

SELECT COUNT(*) AS TransCount FROM trans WHERE res_num = 'A4444' OR
orig_res_num = 'A4444'

Am I better off creating ONE index with both the res_num and orig_res_num
columns >>

It won't make any difference in this case because of the OR, but see the
other messages about row value constructors for other cases.

Tim Young
Elevate Software
www.elevatesoft.com