How to arrange indexes in the database?

Question

MySQL

Tell me how to correctly arrange indexes in a mysql table.
There is a table with fields A,B,C,D,E.
And several queries that make a selection by:
- ​​field A
- fields A, B, C
- fields A, C, D
- fields B, C,
I used to think that for each field that is being sampled, it is enough to do:
ALTER TABLE `table` ADD INDEX( `A`).
And now I'm wondering if I should do
ALTER TABLE `table` ADD INDEX (` A`).
ALTER TABLE `table` ADD INDEX ( ` A,B,C`)
ALTER TABLE `table` ADD INDEX ( ` A,C,D`).
ALTER TABLE `table` ADD INDEX ( `B,C`).
or the same but without the last index. Enlighten, please, how to do it right.

Answer 1

In general, the principles of building indexes are quite simple: an index (except for the primary one) is built to speed up queries. Moreover, if there is a WHERE for two fields, then an index containing both of these fields (if any) will be taken, and it does not matter how many of them there are.
In other words, one index on the fields "A, B, C" will be more "useful" for a query containing some conditions on the fields A and B than two separate indexes on the fields "A" and "B".
In fact, the indices A,B,C and A,C,D will be the same in terms of searching for the field "A". When searching in fields A, B, the first index will be taken. When searching in fields A, C - the second.
In my work, I personally try not to produce unnecessary indexes. After all, if queries on fields B, C are performed extremely rarely, and the table itself is modified quite actively, then building another index will do more harm to the application than good.
Don't be afraid to add the word EXPLAN to the request body and see what indexes it uses. Sometimes the optimizer uses fullscan even if there are indexes.

Answer 2

If all your requests are limited to those four options, then it is enough to leave indexes on B, C, A and A, C, D.

Answer 3

ALTER TABLE `table` ADD INDEX ( ` A`) you can use ALTER TABLE `table` ADD INDEX ( ` A,B,C`) or ALTER TABLE `table` ADD INDEX ( ` A,C,D`) instead, and in general you are doing premature optimization.

Answer 4

All of the above is generally true and correct.
I will note one more important thing. It is important not only which fields are used in the WHERE clause, but also which fields you select.
For example, if you have a query,
SELECT t.C FROM `table` t WHERE t.A > <..> AND t.B < <...>
then the obvious option for an index on columns (A, B) will lose to the less obvious option on columns (A, B, C). Let me explain - if there is an index (A, B), first you will have to find rows by index that satisfy the conditions, and then find the value of tC for these rows among the data of the table itself. In the second case, to calculate tC , it will be possible not to go to the table, but to take the value from the index - such a thing is called Index Only Scan(although, for different DBMS, the name may be different, of course). MySQL / MSSQL / Oracle seems to be able to do it, and PostgreSQL will be able to from version 9.2.
What is the moral? First, you need to use indexes wisely, and you need to know which index is better and why.
Secondly, you are still doing premature optimization. That is, you can create approximately correct indexes, but it is not a fact that they will be optimal for your queries. Above, I gave an example when a seemingly correct index loses to a more specific index for a particular query. Therefore, when the database reaches decent volumes, all complex / heavy queries will need to be viewed through EXPLAIN in an amicable way.
And thirdly, good luck to you :)

Answer 5

the sets you specified cover only 2 indexes:
ALTER TABLE `table` ADD INDEX ( ` A,C,D`).
ALTER TABLE `table` ADD INDEX ( `B,C,A`).
but you should set up slowlog and optimize those queries that actually need to be optimized, and not what you are currently doing.

Answer 6

A few years ago, the book "Oracle for Professionals" was published by Tom Kite.
The chapter on indexes there is universal for most databases.
There is a scan of the book on the Internet.

Answer 7

ALTER TABLE `table` ADD INDEX( `A`).
ALTER TABLE `table` ADD INDEX ( ` A,B,C`)
ALTER TABLE `table` ADD INDEX ( ` A,C,D`).
ALTER TABLE `table` ADD INDEX ( `B,C`)
This is correct if there is a lot of data.
You can add indexes not immediately, but as you notice that the corresponding query slows down.
It may not be necessary to add these indexes at all.