Im considering apply an index of 3 fields (type, status, user_id) on a table. The majority of my queries have WHEREs that use all 3. However, I have a couple of heavily used queries which only use 2 fields (type and status).
My question, is would creating an index with all 3 fields be used efficiently by queries that only really compare 2 fields? Or would it be better to have 2 indexes, one with 3 fields, one with 2?
Cheers!
I know Oracle better than MySQL, but I guess in this case it's the same. The index is usually a B-Tree, which means that if the index is (type, status, user_id) the database can normally still use it to search for (type, status) because it's the first part of the combined index. But that would not be the case if you use (status, user_id), unless there is something like Oracle's INDEX_SKIP_SCAN.
Having a 2nd index covering only two fields might be slightly faster, how much will depend on the data. But if you have two indexes, then inserting data is slower as they both need to be maintained. It also takes more space on disk. So it's a performance-space trade-off that only you can decide.
Yes, you can use the index partially in MySQL the way ewernli mentioned.
Looks like a BTREE-index, that is sorted ( in the order: type, status, user_id) and in this case the one index can be used in both queries: only use 2 fields (type and status)
If you would query on just the user_id, this index can't help you.