Hi, Nikita! On Dec 05, Nikita Malyavin wrote:
Heh, unfortunately comparing refs will not work in conjunction with KEYREAD:
for innodb ref can be a primary key, if it has it, so index is not clustered. The index we're evaluating can be different from a primary key.
In that case some values can be left unfetched with KEYREAD.
All that is because if innodb has primary key, it just does key_copy
In InnoDB every secondary key automatically has primary key at the end, because after finding the key value in a secondary index, InnoDB needs to fetch the corresponding row, so it has to know the PK value for that secondary key. Generalizing, every secondary key has to have the "ref" for the corresponding row. In InnoDB it's PK, in MyISAM it's the row offset, in MEMORY it's pointer. But either way, in any engine, any secondary key knows the ref for the corresponding row. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org