Mariadb: Can I increase performance by having multiple column indexes with different orders?

Question

In my Mariadb table I have two id-columns (A,B), which combined are unique, but not individually. This table is quite large. To speed up queries like

SELECT B WHERE A=?

I have added a primary key (A,B), which worked fine. However sometimes I also need queries

SELECT A WHERE B=? 

which are rather slow. What is the optimal way improve performance here? Is it reasonable to create a second unique index (B,A) on the table?

Moreover, if I do create that second index, does inserting entries into the table become slower, since both indexes are checked? In theory, checking that (A,B) is unique implies that (B,A) is unique, but does Mariadb know that, or does it check both?

Answer 1

Is it reasonable to create a second unique index (B,A) on the table?

Yes, since your predicate in the WHERE clause in the second query is on column B and your original index that leads with column A (i.e. (A, B)) won't be able to be seeked through for this query. So you currently are probably experiencing a less than performant index scan.

Moreover, if I do create that second index, does inserting entries into the table become slower...

Yes.

...since both indexes are checked?

Not so much because of redundant checking for uniqueness, more so it's because of the additional write-overhead there is to maintain two indexes now. But generally that overhead is rather negligible unless you're writing millions of records a second, for example.

There is a general balance though between not enough indexes and too many. It depends on the query use cases, the read vs write workload, the infrastructure behind the database server, and what's acceptable runtimes all around. Typically, up to 5 indexes on a table are fine, and up to 5 columns per index (for being cognizant of index width) is fine. But it's a very loose guideline, and sometimes less or more is the correct number for a specific table's use cases.