If a table already has an INDEX (K1, K2, ...), when is it a good idea to create an INDEX(K1)?

More generally, when INDEX (K1, K2, K3, ...) is present, under what circumstances is it a good idead to create one or several of INDEX (K1), INDEX (K1, K2), aso. ?

  • No. The index with short expression is more compact, so scanning by it needs less pages readings. So you must to find the balance. For example, it may be the most optimal to have 2 indexes - full (K1, K2, ..., K10) and short (K1, K2, K3). Or 3 indexes (by 1-2, by 1-5 and full)... And, for example, while searching by 1-4 the scanning index by 1-3 plus table scan may be more effective then scanning by full index if K4 field have a few possible values (for example, it is boolean).
    – Akina
    Jan 22 '20 at 8:58
  • Its very difficult to determine always unnecessary as it depends on many conditions however generally what you have said is right considering above comments made by Akina. Jan 22 '20 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.