patternsqlMinor
What does having the primary key as the last column in a composite secondary index in an InnoDB table do?
Viewed 0 times
lasttheprimarywhatcolumninnodbhavingsecondarydoescomposite
Problem
Say I have a 1-to-N relationship
I understand that an InnoDB secondary index is essentially a B-tree where the values are the corresponding primary key values for the row.
Now, suppose one person can have thousands of pets and I often want a person's pets in order of
So, if
Thanks
(person_id, pet_id). I have a table where pet_id is the primary key.I understand that an InnoDB secondary index is essentially a B-tree where the values are the corresponding primary key values for the row.
Now, suppose one person can have thousands of pets and I often want a person's pets in order of
pet_id. Then it would matter if records in the secondary index are sorted by (person_id, pet_id) or just person_id with the pet_id's for that person_id being unsorted. Guessing the later.So, if
person_id is non-unique, are records physically sorted by (person_id, pet_id) or JUST pet_id?Thanks
Solution
No. If your table has the InnoDB engine and the
The index includes the
A query like the one you have:
will need to access only the index to get the values and even more, it won't need to do any sort, as the
First, we try with a MyISAM table:
Notice the filesort!
Now, MyISAM with composite index:
Filesort is gone, as expected.
Now lets try the same with InnoDB engine:
No filesort either! Even though the index does not explicitly have the
Lets actually do it, with InnoDB and the composite index:
Identical plans with the previous case.
To be 100% sure, I also run the last 2 cases (InnoDB engine, with single and composite indexes) enabling the
```
DROP TABLE IF EXISTS ... ;
CREATE TABLE ... ;
mysql> INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> INSERT INTO pets (person_id)
SELECT a.person_id+b.person_id-1
FROM pets a CROSS JOIN pets b CROSS JOIN p
PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference.The index includes the
pet_id column as well so values are sorted as (person_id, pet_id) in both cases.A query like the one you have:
SELECT pet_id FROM yourtable
WHERE person_id = 127
ORDER BY pet_id ;will need to access only the index to get the values and even more, it won't need to do any sort, as the
pet_id values are already sorted in the index. You can verify this by looking at the execution plans (EXPLAIN):First, we try with a MyISAM table:
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id)
) ENGINE = myisam ;
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using filesort
1 row in set (0.00 sec)Notice the filesort!
Now, MyISAM with composite index:
DROP TABLE IF EXISTS pets ;
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id, pet_id) -- composite index
) ENGINE = myisam ;
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)Filesort is gone, as expected.
Now lets try the same with InnoDB engine:
DROP TABLE IF EXISTS pets ;
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id) -- simple index
) ENGINE = innodb ; -- InnoDB engine
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)No filesort either! Even though the index does not explicitly have the
pet_id column, the values are there and sorted. You can check that if you define the index with (person_id, pet_id), the EXPLAIN is identical.Lets actually do it, with InnoDB and the composite index:
DROP TABLE IF EXISTS pets ;
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id, pet_id) -- composite index
) ENGINE = innodb ; -- InnoDB engine
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)Identical plans with the previous case.
To be 100% sure, I also run the last 2 cases (InnoDB engine, with single and composite indexes) enabling the
file_per_table setting and adding a few thousands rows in the table:```
DROP TABLE IF EXISTS ... ;
CREATE TABLE ... ;
mysql> INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> INSERT INTO pets (person_id)
SELECT a.person_id+b.person_id-1
FROM pets a CROSS JOIN pets b CROSS JOIN p
Code Snippets
SELECT pet_id FROM yourtable
WHERE person_id = 127
ORDER BY pet_id ;CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id)
) ENGINE = myisam ;
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using filesort
1 row in set (0.00 sec)DROP TABLE IF EXISTS pets ;
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id, pet_id) -- composite index
) ENGINE = myisam ;
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)DROP TABLE IF EXISTS pets ;
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id) -- simple index
) ENGINE = innodb ; -- InnoDB engine
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)DROP TABLE IF EXISTS pets ;
CREATE TABLE table pets
( pet_id int not null auto_increment PRIMARY KEY,
person_id int not null,
INDEX person_ix (person_id, pet_id) -- composite index
) ENGINE = innodb ; -- InnoDB engine
INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
mysql> EXPLAIN SELECT pet_id FROM pets
WHERE person_id = 2
ORDER BY pet_id asc \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pets
type: ref
possible_keys: person_ix
key: person_ix
key_len: 4
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)Context
StackExchange Database Administrators Q#136009, answer score: 7
Revisions (0)
No revisions yet.