>CREATE INDEX IDX_BILLING_ACCOUNT_SELLER_ID_STATUS ON OB_BILLING_ACCOUNT(SELLER_ID,STATUS);
>possible_keys: IDX_BILLING_ACCOUNT_STATUS,IDX_BILLING_ACCOUNT_SELLER_ID,IDX_BILLING_ACCOUNT_SELLER_ID_STATUS
key: IDX_BILLING_ACCOUNT_SELLER_ID
Optimizer isn't using the new index, instead its using the old (redundant index).
1. Use index hints so that optimizer shall use new index i.e. IGNORE INDEX (IDX_BILLING_ACCOUNT_SELLER_ID)
Learn more about index hints:
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
And provide EXPLAIN output of the same sql
2. Also try this index:
INDEX (STATUS, SELLER_ID)
in this case you may have to add index hints to ignore following index i.e. IGNORE INDEX (IDX_BILLING_ACCOUNT_STATUS, IDX_BILLING_ACCOUNT_SELLER_ID)
And provide EXPLAIN output of the same sql
Once you are happy with new index performance, drop redundant indexes
Edited 1 time(s). Last edit at 08/01/2012 06:07AM by Aftab Khan.