Where the open source database community meets: Use code PERCONA75 and secure your spot for Percona Live. Register

Downloads

Blog

MySQL Optimizer and Innodb Primary Key

October 3, 2006

Author

Peter Zaitsev

Insight for DBAs

Share this Post:

Innodb primary key is special in many senses and I was always wondering how well MySQL is integrated with Innodb to take advantage of these special features.

Lets see which things work and which things do not:

I used the following simple table for tests:

CREATE TABLE `innodb` (
  `id` int(10) unsigned NOT NULL,
  `a` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `innodb` (

`id` int(10) unsigned NOT NULL,

`a` int(11) default NULL,

PRIMARY KEY (`id`),

KEY `a` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

“myisam” is same table created with MyISAM storage engine used to show difference:

MySQL Optimizer correctly knows Innodb tables is clustered by primary key in the sense it would not be faster to do external filesort than to do lookups in primary key order:

mysql> explain select * from innodb order by id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pktest
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
        Extra:
1 row in set (0.00 sec)

mysql> explain select * from myisam order by id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: myisam
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using filesort
1 row in set (0.00 sec)

mysql> explain select * from innodb order by id G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: pktest

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 6

Extra:

1 row in set (0.00 sec)

mysql> explain select * from myisam order by id G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: myisam

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 6

Extra: Using filesort

1 row in set (0.00 sec)

MySQL Optimizer is also able to figure out every key also holds primary key value so primary key value can be read from index, making some queries index covered which previously was not: Notice “Using Index” difference

mysql> explain select id from innodb where a=3 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: innodb
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select id from myisam where a=3 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: myisam
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select id from innodb where a=3 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: innodb

type: ref

possible_keys: a

key: a

key_len: 5

ref: const

rows: 1

Extra: Using where; Using index

1 row in set (0.00 sec)

mysql> explain select id from myisam where a=3 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: myisam

type: ref

possible_keys: a

key: a

key_len: 5

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

MySQL However is unable to benefit from the fact each index internally has primary key as last column, so key on (a) is effectively key on (a,id) which means MySQL could skip filesort if ordering is done by primary key:

mysql> explain select id from innodb where a=3 order by id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: innodb
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)

mysql> explain select id from innodb where a=3 order by id G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: innodb

type: ref

possible_keys: a

key: a

key_len: 5

ref: const

rows: 1

Extra: Using where; Using index; Using filesort

1 row in set (0.00 sec)

Filesort should be avoided in this case which it is not. I now filed it as a
bug while I do not really think it would be fixed soon

0 0 votes

Article Rating

9 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Roland Volkmann

19 years ago

Hello Peter,

as long as the bug is not fixed, you can define key a as “KEY a (a,id)” to avoid filesort. I did so in my projects.

With best regards

Roland

Author

Peter Zaitsev

19 years ago

Roland yes. The solution you’re mentioning does work and it is a way to get such behavior for non-Innodb tables.
My point is simply it could be done for Innodb with given index structure but it is not.

Also quite curious if key (a,id) is used is Innodb is smart enough to notice it already had id in the end so it does not have to be added second time.

grantsucceeded

19 years ago

Peter:
It is not true that primary key lookup is faster than filesort, or at least should not be true.

If one is sorting a significant number of rows, sort is faster because it can do sequential IOs then O(logN) sort instead of random IOs.

Author

Peter Zaitsev

19 years ago

Why is that ?

The choice is basically if you access data by primary key and send it or you access data by primary key to sort it and return back to the client ?

Innodb full table scan will be reading via Innodb key and the data comes sorted already so it will be just overhead to sort it again.

Raj

18 years ago

Will Primary key improve performance ?

Geo

18 years ago

This is now fixed, at least my tests show so

CREATE TABLE pricesbig_inno (
id int(10) unsigned NOT NULL,
url_id int(10) unsigned NOT NULL,
product_id int(10) unsigned NOT NULL,
PRIMARY KEY (product_id,id),
KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> EXPLAIN SELECT id, product_id FROM pricesbig_inno WHERE id =1515;
+—-+————-+—————-+——+—————+——+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+—————-+——+—————+——+———+——-+——+————-+
| 1 | SIMPLE | pricesbig_inno | ref | id | id | 4 | const | 1 | Using index |
+—-+————-+—————-+——+—————+——+———+——-+——+————-+
1 row in set (0.00 sec)

Author

Peter Zaitsev

18 years ago

What is fixed ?

This seems to be similar to my example:

#
mysql> EXPLAIN SELECT id FROM innodb WHERE a=3 G
#
*************************** 1. row ***************************
#
id: 1
#
select_type: SIMPLE
#
TABLE: innodb
#
type: ref
#
possible_keys: a
#
KEY: a
#
key_len: 5
#
ref: const
#
rows: 1
#
Extra: USING WHERE; USING INDEX
#
1 row IN SET (0.00 sec)

Which already had using index.

Cyril Scetbon

17 years ago

It seems to be not the case anymore in 5.1 :

mysql> EXPLAIN SELECT id FROM innodb WHERE a=3 ORDER BY id G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: innodb
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.51 sec)

Author

Peter Zaitsev

17 years ago

Cyril,

Thank you for update. Indeed problems are getting fixed so you have to look at the old posts with care.
In particular as developers often read this blog it is not a surprise 🙂