Does order of index matter?

Help Query Tuning
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 SingleStoreDB Cloud · SingleStore Documentation.

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