How does searching on combined indexes work?

Question

MySQL

Hello!

The question is this: Let's say there is a test table, the fields in it are:
id, language_id, english_word, word, word_id

id, of course, let it be PK.
If I create 2 combined indexes:
language_id, english_word
and
word, word_id

And then I look for the table like this:

WHERE language_id=...
AND english_word=...
AND word=...
AND word_id=...

Are the two previously created indexes included in the work? Those. does it give rise?

I know it's best to check it out yourself! Just don't have time yet and hope someone here already has some solid knowledge on this.

Thanks for the help!

Answer 1

The scheduler has this option. bitmap index scan can combine a pass over multiple indexes. Whether the scheduler will use this plan or prefer something else, and, moreover, whether it will be cheaper - depends on the statistics of data distribution, settings, hardware capabilities.
How the scheduler in your system will decide - explain will answer. How much something really costs in runtime - will answer explain (analyze,buffers)
Didn't pay attention to the second tag, it was about postgresql.
mysql can do the same, it's called Index Merge. Whether it will be used is again at the discretion of the scheduler.

Answer 2

Only one index will be used, in whole or in part.
Depends on the selectivity of the index for this condition.

Answer 3

do EXPLAIN ..... and it answers all your questions