Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 3 of 3 total |
Multi-column index and partial-optimized query |
Fri, Apr 8 2022 11:40 AM | Permanent Link |
VS | Hi,
I create table and index this way: CREATE TABLE IF NOT EXISTS WaitQueue ( ID AUTOINC, DeliveryID INTEGER, StartID INTEGER, ContactID INTEGER, PRIMARY KEY (ID)); CREATE INDEX IF NOT EXISTS "MultiIdx ON "WaitQueue" ("DeliveryID","StartID","ContactID"); Then I perform query SELECT ID FROM WaitQuery WHERE DeliveryID=10 AND StartID=20 AND ContactID=30 But query plan said: The expression: DeliveryID = 10 AND StartID = 20 AND ContactID = 30 has been rewritten and is PARTIALLY-OPTIMIZED, covers 5409 rows or index keys, costs 1628109 bytes, and will be applied to the TriggerWaitQueue table (WaitQueue) before any joins Why query only partial optimized ? I have multi-column index for all searching columns and I use columns in WHERE conditions in same order like in index ? Where I wrong ? How to make this query fully optimized ? In what cases I can get benefits from multi-column indexes ? Thanks ! |
Sat, Apr 9 2022 7:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | VS
If my aging memory is correct you need a separate index for each column. Multi-column indices are of benefit in the ORDER BY clause not the WHERE clause. Roy Lambert |
Sat, Apr 9 2022 8:02 AM | Permanent Link |
VS | Roy Lambert wrote:
VS If my aging memory is correct you need a separate index for each column. Multi-column indices are of benefit in the ORDER BY clause not the WHERE clause. Roy Lambert Thank you, Roy ! I will try ! |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |