Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Indexed on 2 columns |
Sat, Aug 11 2012 12:43 AM | Permanent Link |
IQA | 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. |
Sat, Aug 11 2012 3:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sat, Aug 11 2012 4:54 AM | Permanent Link |
IQA | > 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. |
Sat, Aug 11 2012 5:00 PM | Permanent Link |
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') -- Fernando Dias [Team Elevate] |
Sat, Aug 11 2012 8:22 PM | Permanent Link |
IQA | > 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! |
Tue, Aug 14 2012 4:35 PM | Permanent Link |
Barry | 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. |
Tue, Aug 14 2012 5:16 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Wed, Sep 5 2012 8:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |