4

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:

  • (a)
  • (a, b)
  • (a, b, c, id)

Is this valid for InnoDB as well?

Or should I define the necessary ones explicitly?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Pavel Koryagin
  • 143
  • 3

2 Answers2

2

This is not at all the case for InnoDB. Here is why:

For InnoDB table, A primary key is InnoDB is stored in gen_clust_index, the Clustered Index. Every secondary index has an internal key back to the gen_clust_index.

Given this background on PRIMARY KEY storage, now for differences:

MyISAM

Given Primary Key id and index a,b,c, the implicit keys are

  • (a)
  • (a, b)
  • (a, b, c)

This is always true of the index a,b,c regardless of the index being unique or non-unique. The column id is not known to the index. There is a rowid that links each .MYI entry back to a row in the .MYD, but but no direct knowledge of id. The id is quite visible in the primary key.

InnoDB

Given Primary Key id and index a,b,c, the implicit keys are

  • (a, id)
  • (a, b, id)
  • (a, b, c, id)

The reason id is known is due to the fact that every secondary index entry has an implicit key back to the gen_clust_index.

Please see my past posts on the gen_clust_index.

PROOF OF THIS...

Let's create two sample tables. One with MyISAM and the other InnoDB

  • Same Data
  • Same Columns
  • Same Indexes

Here is the code:

DROP DATABASE IF EXISTS pavel;
CREATE DATABASE pavel;
USE pavel
CREATE TABLE rolando_myisam
(
    id int not null auto_increment,
    a int not null,
    b int not null default 99,
    c int not null default 99,
    primary key (id),
    key abc_ndx (a,b,c)
) ENGINE=MyISAM;
CREATE TABLE rolando_innodb LIKE rolando_myisam;
ALTER TABLE rolando_innodb ENGINE=InnoDB;
INSERT INTO rolando_myisam (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
INSERT INTO rolando_innodb (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;

Here it is loaded:

mysql> DROP DATABASE IF EXISTS pavel;
Query OK, 2 rows affected (0.23 sec)

mysql> CREATE DATABASE pavel;
Query OK, 1 row affected (0.00 sec)


mysql> USE pavel
Database changed
mysql> CREATE TABLE rolando_myisam
    -> (
    ->     id int not null auto_increment,
    ->     a int not null,
    ->     b int not null default 99,
    ->     c int not null default 99,
    ->     primary key (id),
    ->     key abc_ndx (a,b,c)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)


mysql> CREATE TABLE rolando_innodb LIKE rolando_myisam;
Query OK, 0 rows affected (0.13 sec)


mysql> ALTER TABLE rolando_innodb ENGINE=InnoDB;
Query OK, 0 rows affected (0.95 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> INSERT INTO rolando_myisam (a)
    -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
    -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
    -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
    -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
    -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0


mysql> INSERT INTO rolando_innodb (a)
    -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
    -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
    -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
    -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
    -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0


mysql>

Here two queries to show a retrieval of the id columns:

SELECT id FROM rolando_myisam;
SELECT id FROM rolando_innodb;

Let's explain them:

mysql> EXPLAIN SELECT id FROM rolando_myisam;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_myisam | index | NULL          | PRIMARY | 4       | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql>

Notice the Query optimizer chose the PRIMARY KEY for the MyISAM table, but chose the abc_ndx key for the InnoDB table. What does this show? The abc_ndx index for the MyISAM table cannot see the id column. Thus, id is not implicit for MyISAM.

Let's try a different type of query:

EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;

Look at the EXPLAIN plans:

mysql> EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | rolando_myisam | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql>

Again, abc_ndx cannot be used for getting id because it is not implicit for a MyISAM index. It is definitely implicit for InnoDB.

Let's try another different type of query:

EXPLAIN SELECT id FROM rolando_myisam where a = 12;
EXPLAIN SELECT id FROM rolando_innodb where a = 12;

Look at the EXPLAIN plans:

mysql> EXPLAIN SELECT id FROM rolando_myisam where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | rolando_myisam | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | NULL  |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | Using index |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)


mysql>

Once Again,abc_ndx index cannot be used for getting id because it is not implicit for a MyISAM index. It is implicit for InnoDB. Note that the InnoDB lookup use the index only. This is why the Extra in the EXPLAIN plan had Using index for InnoDB, but had nothing for MyISAM. The MyISAM lookup had to reach into the .MYD to get id.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

Given your example, and considering that you're using B-TREE indexes:

For MyISAM, you will have and index on (a, b, c). Each leaf of the index will include the ID, as a pointer to the actual row from the table. So an index leaf will look similar to (ID, a, b, c).

Same for InnoDB.

Read this as a starting poing: https://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql

Community
  • 1
MTIhai
  • 704
  • 5
  • 10