How to have a covering index used for the updates?

Question

I have a db table with"

id bigint NOT NULL AUTO_INCREMENT (primary key)
customer_ref varchar(64) NOT NULL
customer_counter int NOT NULL
description varchar(255) NOT NULL

I have also the following indexes:

PRIMARY KEY(id)
UNIQUE KEY c_ref (customer_ref)
KEY c_c (customer_counter)
KEY c_desc(customer_ref, description, customer_counter)

I have the following update statements:

UPDATE CustomerReferences SET customer_counter=100, description='invalidate due to X123' WHERE customer_ref='1000001' and description <> 'immutable'
UPDATE CustomerReferences SET customer_counter=100, description='invalidate due to X123' WHERE id=2 and description <> 'immutable'

Problem: I am using the EXPLAIN and I can't find a way to make the query filter the rows directly from the index. It always uses the index of the primary key or the unique key (c_ref). How can I index the table so that these updates filter/update directly via the index? Note that the particular updates should be no-ops as if I do select count(*) based on the where statement I get 0 rows due to the condition description <> 'immutable'

score 1 · Answer 1 · answered Oct 20 '24 at 23:36

Short answer: Not worth worrying about.

Long answer:

Let's look at these:

UNIQUE KEY c_ref (customer_ref)
KEY        c_desc(customer_ref, description, customer_counter)

The first one says that customer_ref='1000001' cannot refer to more than one row. The second is almost redundant.

Secondary indexes (such as both of those) work by first searching part or all of the index's BTree, then using the PK (id) to locate the row in the data's BTree.

The UPDATE Optimizer probably looks at both of those and decides to use the UNIQUE index. Yes, the other index may be a tiny bit better if description <> 'immutable'. The savings is so small that I think the developers will brush your suggestion off.

Consider rearranging the index's columns if you have other queries that use the composite index.

score 0 · Answer 2 · answered Oct 21 '24 at 19:02

I don't think the benefit of a covering index applies to UPDATEs (or DELETEs, for what it's worth), because if there are any matching rows, it'll have to change the row in the clustered index anyway. It might also have to change more than one secondary index, if the column you set is present in multiple indexes.

I created your table and tested EXPLAIN.

In the first query, it considers c_desc as a possible key, but it chose to use c_ref. In general, the optimizer tends to prefer unique indexes, and also tends to prefer smaller indexes.

--------------
EXPLAIN
UPDATE CustomerReferences SET customer_counter=100, description='invalidate due to X123' 
WHERE customer_ref='1000001' and description <> 'immutable'
--------------
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: CustomerReferences
   partitions: NULL
         type: range
possible_keys: c_ref,c_desc
          key: c_ref         <-- optimizer prefers the unique index
      key_len: 258
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where

In the second query, it uses the PRIMARY key to do the lookup based on the id column. This is the clustered index, so it's efficient. It will match at most one row, and testing that row for the other condition won't be a big cost. Using another index won't be an improvement.

--------------
EXPLAIN
UPDATE CustomerReferences SET customer_counter=100, description='invalidate due to X123' 
WHERE id=2 and description <> 'immutable'
--------------
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: CustomerReferences
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where

I think it's kind of interesting that EXPLAIN shows type: range even though the lookup is for a constant value against a unique index. I think this is a side-effect of using EXPLAIN for UPDATE.

If I try the same test with a similar SELECT, it shows type: const.

--------------
EXPLAIN
SELECT * FROM CustomerReferences 
WHERE customer_ref='1000001' and description <> 'immutable'
--------------
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CustomerReferences
   partitions: NULL
         type: const
possible_keys: c_ref,c_desc
          key: c_ref
      key_len: 258
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

You mentioned in a comment that you dropped the c_ref unique index. I tried that test, and this is what happened:

--------------
EXPLAIN
UPDATE CustomerReferences SET customer_counter=100, description='invalidate due to X123' 
WHERE customer_ref='1000001' and description <> 'immutable'
--------------
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: CustomerReferences
   partitions: NULL
         type: range
possible_keys: c_desc
          key: c_desc    <-- it does choose the compound index
      key_len: 1280
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: Using where; Using temporary
1 row in set, 1 warning (0.00 sec)

It does choose the compound index, but this doesn't make it a covering index, because it still has to update the row in the clustered index as well.

Updating based on the id works the same as before, which I would expect.

--------------
EXPLAIN
UPDATE CustomerReferences SET customer_counter=100, description='invalidate due to X123' 
WHERE id=2 and description <> 'immutable'
--------------
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: CustomerReferences
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where

How to have a covering index used for the updates?

2 Answers2