MySQL – Find and Remove Redundant Indexes

1. In the example you show, the expression would use the index on criteria to resolve which rows to retrieve, and the leaf nodes in the index contain references to the corresponding rows, either by physical location in the case of MyISAM or by primary key value in the case of InnoDB. That way it avoids a full table scan.

   Note that if the query optimizer thinks your query matches too much of the table, it will ignore the index, because it's just as fast to do the table-scan. By analogy, this is why no one bothers to put the word "the" in the index at the back of a book.

2. No, MySQL requires that an index exists for the foreign key. If an index already exists, InnoDB will use it, but it will create one if necessary. I have found some corner cases where you can trick InnoDB by dropping the index after defining the foreign key constraint, but this would not be advisable, because the index is important for checking referential integrity quickly.

3. It's true that insert/update/delete needs to update indexes to reflect values added or removed. But it's not as bad as you might fear. Also, InnoDB implements a "change buffer" which records how your update affects secondary indexes, and reconciles it with the whole index later.

4. InnoDB and MyISAM support only BTREE indexes. InnoDB also has an internal feature called the adaptive hash which does do a hash lookup for frequently-requested index entries. But this is completely automatic and transparent; you don't have to define any index or tune any config parameters.

This is a known limitation of InnoDB. It was reported as a bug way back in 2005:
Bug #11305 On Update Cascade not working with two references to the same field

The bug was acknowledged but not fixed. The following was added to the manual section http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html:

Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Based on a workaround suggested in the bug, I got the update to work:

mysql> SET SESSION foreign_key_checks=0;

mysql> UPDATE father  SET `col1` = 'UPDATE_MY_ID_PLEASE'  WHERE `col1` = 'A' AND `col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE closure SET `a-col1`='UPDATE_MY_ID_PLEASE' WHERE `a-col1` = 'A' AND `a-col2` = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE closure SET `d-col1`='UPDATE_MY_ID_PLEASE' WHERE `d-col1` = 'A' AND `d-col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM father ; SELECT * FROM closure;
+---------------------+------+------+
| col1                | col2 | col3 |
+---------------------+------+------+
| A                   |    2 | A2   |
| B                   |    1 | B1   |
| B                   |    2 | B2   |
| UPDATE_MY_ID_PLEASE |    1 | A1   |
+---------------------+------+------+
4 rows in set (0.00 sec)

+---------------------+--------+---------------------+--------+--------+
| a-col1              | a-col2 | d-col1              | d-col2 | length |
+---------------------+--------+---------------------+--------+--------+
| A                   |      2 | A                   |      2 |      0 |
| A                   |      2 | B                   |      2 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | B                   |      1 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | UPDATE_MY_ID_PLEASE |      1 |      0 |
+---------------------+--------+---------------------+--------+--------+