MySQL Forums
Forum List  »  InnoDB

Re: Deadlock Detection in InnoDB storage engine
Posted by: Aftab Khan
Date: August 01, 2012 06:05AM

>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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Deadlock Detection in InnoDB storage engine
1295
August 01, 2012 06:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.