Does order of index matter?


#1

Lets say I have an index like below

CREATE INDEX ABC USING BTREE
ON table(BusinessDate, Account, Type)
GO

I have query patterns like

SELECT * FROM table
WHERE BusinessDate < someValue
AND Type = ‘someValue’

SELECT * FROM table
WHERE BusinessDate < someValue
AND Type = ‘someValue’
AND Account = ‘someValue’

Would it help if I re-arrange the order of fields in the index to below?
CREATE INDEX ABC USING BTREE
ON table(BusinessDate, Type, Account)
GO


#2

Yes, order matters. Just like B-tree indexes in other databases, the index can be used efficiently when filters match a prefix of the index columns - to be more specific, the index can be efficiently seeked into on equality filters on the first k columns of the index optionally followed by an inequality/range filter on the k+1st column.

In your example, index order Type, BusinessDate or Type, Account, BusinessDate would be good options.

See also https://docs.memsql.com/tutorials/v6.8/optimizing-table-data-structures/#choosing-rowstore-keys.

You can also run explain on your query to see how the index is being used.