All geek questions in one place

Will a 3-field index work for 2 field queries?

Im considering using an index of 3 fields (type, status, user_id) in a table. Most of my queries have WHERE, which use all 3. However, I have a few heavily used queries that use only 2 fields (type and status).

My question is, will creating an index with all 3 fields be effectively used by queries that really really compare 2 fields? Or would it be better to have 2 indexes, one with 3 fields, one with 2?

Hooray!

+3
source share
3 answers

I know Oracle better than MySQL, but I think this is the same in this case. An index is usually a B-Tree, which means that if an index (type, status, user_id), the database can usually use it to search (type, status)because it is the first part of the combined index. But this is not the case if you are using (status, user_id), if only something like Oracle INDEX_SKIP_SCAN.

Having a second index covering only two fields can be a little faster, how much will depend on the data. But if you have two indexes, then inserting data is slower, as both of them must be supported. It also takes up more disk space. So this is a performance tradeoff that you can only solve.

+4
source

, MySQL , .

+1

BTREE-, ( : , , user_id), : 2 ( )

If you request only user_id, this index will not be able to help you.

0
source

Source: https://habr.com/ru/post/1733860/

More articles:


All Articles