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 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. – Learning_DBAdmin Jan 22 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.