2

If I have a table like so:

table T ( account_id bigint, event_date datetime ... );

and later make queries like so:

select ...
from T
where account_id = 'ABC'
  and event_date >= '2016-01-01'

Does the order of columns in the index matter, ie:

key (`account_id`, `event_date`)

Or

key (`event_date`, `account_id`)

Thanks!

3

When building an index for a = constant AND b >= ..., put any and all '=' columns first; end with one 'range': INDEX(a, b). In your case INDEX(account_id, event_date).

You do need a PRIMARY KEY on your table, but it is unclear whether this is suitable. The PK must be unique.

For more discussion, see my cookbook. The info is the same for MySQL as for MariaDB. If you prefer, see MySQL: Building the best INDEX for a given SELECT

  • When I tried both ways your recommended approach yielded significantly better result in terms execution time and DESCRIBE reported row count. – Slawomir Aug 19 '16 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.