0

So has anyone else applied a covering index in MySQL and gotten improved performance but when you run explain, it shows using where, using temporary under the extra column? Any thoughts why this is the case? I'm happy about the performance improvement but the engineer in me wants to know why the extra column is not saying "using index". If you need me to provide details, I will but this question is directed to the experts who have already experienced this with MySQL and know why. I would add that in my particular case, I applied a covering index to an update statement. All the columns in the query were included in the composite (covering) index. Here is an example of exactly what I did:

Table A has the following properties: 25,000 rows.
Has the following columns: one, two, three, four, five, six, seven, eight, and nine.

My covering index query target:

update table A set one = 'somevalue', two = 'somevalue2', four = 'somevalue3', five = 'somevalue4' where seven = 'somevalue5' and eight = 'somevalue6'

covering index on table A: idx_covering(seven,eight, one,two,four,five)

1

The order of the index matters.

Your update query uses seven and eight. There must be at the left most begining of the index. Because both are with = value based where criteria the order doesn't matter.

create index udx_78 on A (seven, eigth)

Putting the updated columns into the index doesn't help an update query. If it was aselect query, then the query would return those columns from the index.

See how mysql uses indexes.

Welcome to DBA stack exchange. Please include the explain output next time along with show create table {tablename} as it conveys the message faster.

1
  • 1
    Additionally, including the 'updated columns' in the index hurts a little -- now it must update the index when doing that UPDATE.
    – Rick James
    Jul 26 '20 at 5:38

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.