on primary keys

5.1.46 has this change:

Performance: While looking for the shortest index for a covering index scan, the optimizer did not consider the full row length for a clustered primary key, as in InnoDB. Secondary covering indexes will now be preferred, making full table scans less likely.

In other words, if you have covering index on * (which is quite common on m:n mapping tables), use it rather than PK. As I have spent my time getting indexing right and having PKs be based on primary access pattern and SKs on secondary access pattern, I hereby not welcome the new change that suddenly reverses the behavior in late GA version.

Not good, when mysqldump queries end up taking 6 days instead of previous half an hour, not good at all.

Update: Oh, MariaDB has this reverted, from their change log:

mybug:39653: reverted as invalid

If only upstream MySQL would take note ;-)

7 thoughts on “on primary keys”

  1. To be exact: we did not have it reverted in our releases: the patch for #39653 was not accepted after my review and was removed from our internal merge tree. So MariaDB never contained this patch in any release tree.

  3. Domas, I’ve filed bug #55656 on this. Please provide feedback there, particularly about the suggested fix.

  4. If only upstream MySQL would take note ;-)

    Actually, if Oracle has imposed the same clean-room rules on MySQL as they would for their own engineers, I’m not sure MySQL devs are allowed to look at MariaDB code or changelog. For this one, yes, it is not copyrightable “IPR” that we excluded a patch, but if you look at the adjacent one, they probably are not allowed to look at Igor’s optimization and copy it.

    Now, if we only could co-operate with them for real then I wouldn’t be speculating about this in the first place….

    1. As you probably know, but reiterating again, we accept contributions. We already accepted a few contributions from MariaDB engineers. Likewise for reversals.

  5. The 17 August patch for this uses the primary key when there’s both a PK and all column secondary index. Expected to be in 5.1.51, just missed the cut off date for 5.1.50. Subject to change, as usual, until it’s actually shipped. For anyone who’s comfortable building the server this is an easy patch to apply.

Comments are closed.

%d bloggers like this: