Mysql – Does MySQL updated index if index key is not updated

indexMySQL

Say I have a table with 3 columns: ID (PK), Name, Age.

Say ID has an index on it, name has an index on it, and there is another index which combines id and name. Lets say I now have an update statement which looks like this:

UPDATE table SET AGE = 69 WHERE id = 'some_id';

I am only updating the AGE column, so will MySQL still update the indexes even though non of the indexed columns were modified or will it leave the indexes alone?

Best Answer

It is logical to think that the current databases are smart enough to not update the index which data have not changed.

But this logic should be straight supported by Mysql manual. And I've really found this statement on page which describes Speed of UPDATE Statements:

An update statement is optimized like a SELECT query with the additional overhead of a write. The speed of the write depends on the amount of data being updated and the number of indexes that are updated. Indexes that are not changed do not get updated.

Related Solutions

Mysql – Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

The first thing I noticed is that the id column is not indexed. For all intents and purposes, your acting PRIMARY KEY is FTS_DOC_ID since it is the only unique key present without a PRIMARY KEY definition.

Since id is not indexed, I could see mysqld wanting to do a full table scan to fulfill the query. You should want to create a UNIQUE KEY on id as well if you intend on having two different unique keys and having two different references to one row.

Note that FTS_DOC_ID has the auto_increment attribute. There is no need to increment it manually, like you did with

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

In fact, by doing this, you are actually taking the FTS_DOC_ID and changing it on id.

Looking back at your first query

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

Why would this be a problem (and essentially a bug) ? Again, this goes back to id not being indexed.

When a column is not indexed, the Clustered Index of the table (where the PRIMARY KEY resides) is unaware of the presence of id. How is this so?

According to the Book

oxcnd

Chapter 12 Pages 260,261 (Subheading Secondary Indexes) says the following:

What about other indexes? How are they structured, and what is their relationship with the data found in the clustered index?

These additional indexes are know as "secondary indexes," and you can create as many of them as you need for an InnoDB table. Because our sample table schema definition requests an index on the last_name column, InnoDB creates one secondary index on this value.Each entry in the secondary index contains the primary key value from the clustered index,thereby facilitating linkage back to the data.

Your First Query

Since id is not indexed your first update failed. MySQL (eh Oracle) should have caught this problem. So, it is a bug.

Your Second Query

The update works because you accessed the Clustered Index indirectly by trying to manually update the FTS_DOC_ID column.

Your Third Query

It worked because you accessed no indexes at all, neither on id nor on body. In that instance, a full table scan would easily fulfill this request.

Epilogue

If I were you, I would report this as a bug ASAP because MySQL 5.6's FULLTEXT indexing for InnoDB is what people want these days and query plans should be accommodating it with WHERE clauses whether accessing with the Clustered index or not.

MySQL – Does Multi-Column Index Order Matter?

In your case it's a choice between bad and worse. gender is low selective (read MySQL has to read at least a half of index), if age comes first, gender part won't be used at all (because of < ). (gender, age) seems to be a bit better anyway.

Check with SHOW STATUS LIKE 'Handler%' which index makes MySQL to read less rows.

mysql> FLUSH STATUS;
mysql> SELECT name WHERE gender='M' and age<18;
mysql> mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 38    |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 154   |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 35    |
| Handler_read_key           | 62    |
| Handler_read_last          | 0     |
| Handler_read_next          | 153   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 5947  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 1     |
| Handler_write              | 617   |
+----------------------------+-------+