Search code examples
mysqlcomposite-index

Can I have multi-order index for composite/multi-column index in MySQL?

I have an invoices table like this:

| id   | client_id | is_recurring |
|----- |-----------| -------------|
| 1    | 2121      | 0            |
| 2    | 7434      | 1            |

Now throughout my application, I might have following queries:

select * from invoices where client_id = 2121 and is_recurring = 0;

or

select * from invoices where is_recurring = 0 and client_id = 2121;

or any other order of where clause.

I already have index on client_id an is_recurring separately.

But for composite index, should I create composite index on

compost_index('client_id','is_recurring')

or

compost_index('is_recurring','client_id')

or Both?

Note that both are in different order. So for performance of different order searches? should I created composite index with multiple order/direction?

Update: Also if I have a date column which I will use for comparing greater or less or order by, what combinations of composite indexes I should use?

Solution

  • As a rough rule of thumb, you might expect better performance in a two column index by placing the more restrictive (higher cardinality) column first. So, I would suggest:

    CREATE INDEX ON invoices compost_index (client_id, is_recurring)

    This index, if used, would let MySQL discard a large portion of the index just by filtering by the client_id. The is_recurring column, on the other hand, presumably only takes on the two values of 0 and 1. So, filtering by this might not allow for many records to be discarded when scanning the index.