HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Are there implicit indexes in InnoDB like MyISAM?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlinnodbdbastackoverflowmysqlmyisamindex
implicitareinnodblikeindexesmyisamthere

Problem

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?

Solution

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 |
+----+-------------+----------------

Code Snippets

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;
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>
SELECT id FROM rolando_myisam;
SELECT id FROM rolando_innodb;
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>
EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;

Context

StackExchange Database Administrators Q#37643, answer score: 2

Revisions (0)

No revisions yet.