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 !

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

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 !