|
Discovered this while working on MDEV-10325:
https://jira.mariadb.org/browse/MDEV-10325?focusedCommentId=84856&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-84856
I've narrowed it down to two tables, with identical columns and indexes, except
that the order of indexes is different:
create table t200 (
|
index_id bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
|
index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL ,
|
index_object_id int(10) unsigned NOT NULL DEFAULT '0' ,
|
index_date_updated int(10) unsigned DEFAULT NULL ,
|
|
PRIMARY KEY (index_id),
|
KEY object (index_class(181),index_object_id),
|
KEY index_date_updated (index_date_updated)
|
) engine=innodb;
|
create table t200swp (
|
index_id bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
|
index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL ,
|
index_object_id int(10) unsigned NOT NULL DEFAULT '0' ,
|
index_date_updated int(10) unsigned DEFAULT NULL ,
|
|
PRIMARY KEY (index_id),
|
KEY index_date_updated (index_date_updated),
|
KEY object (index_class(181),index_object_id)
|
) engine=innodb;
|
Now, look at the key defintions.
(gdb) p table->s.table_name
|
$209 = {str = 0x7fff501ae65c "t200", length = 4}
|
(gdb) p table->key_info[0]
|
$210 = {key_length = 8, flags = 1, user_defined_key_parts = 1,
|
usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 1,
|
ext_key_part_map = 0, ... name = 0x7fff501a4f99 "PRIMARY",... }
|
(gdb) p table->key_info[1]
|
$211 = {key_length = 188, flags = 104, user_defined_key_parts = 2,
|
usable_key_parts = 2, ext_key_parts = 2, ext_key_flags = 104,
|
ext_key_part_map = 0, ... name = 0x7fff501a4fa1 "object" ... }
|
(gdb) p table->key_info[2]
|
$212 = {key_length = 5, flags = 64, user_defined_key_parts = 1,
|
usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 64,
|
ext_key_part_map = 0, ... name = 0x7fff501a4fa8 "index_date_updated"}
|
(gdb) p table->s.table_name
|
$216 = {str = 0x7fff501b25bc "t200swp", length = 7}
|
(gdb) p table->key_info[0]
|
$217 = {key_length = 8, flags = 1, user_defined_key_parts = 1,
|
usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 1,
|
ext_key_part_map = 0, ... name = 0x7fff501b4479 "PRIMARY"}
|
(gdb) p table->key_info[1]
|
$218 = {key_length = 5, flags = 64, user_defined_key_parts = 1,
|
usable_key_parts = 2, ext_key_parts = 2, ext_key_flags = 131136,
|
ext_key_part_map = 1, ... name = 0x7fff501b4481 "index_date_updated"}
|
(gdb) p table->key_info[2]
|
$219 = {key_length = 188, flags = 104, user_defined_key_parts = 2,
|
usable_key_parts = 2, ext_key_parts = 2, ext_key_flags = 104,
|
ext_key_part_map = 0, ... name = 0x7fff501b4494 "object", }
|
Compare the attributes of 'index_date_updated' key:
- t200swp has user_defined_key_parts=1, ext_key_parts=2, correct.
- t200 has: user_defined_key_parts=1, ext_key_parts=1. Why 1?
I could understand that there may be some limitations that prevent inclusion of
PK components into the secondary key (like maximum index tuple length perhaps?)
What I don't understand is why the order of indexes in the table plays a role.
|