Why MySQL multiple-column index is overpopulated?

Posted by actual on Stack Overflow See other posts from Stack Overflow or by actual
Published on 2010-12-25T08:27:18Z Indexed on 2010/12/25 8:54 UTC
Read the original article Hit count: 205

Filed under:
|
|

Consider following MySQL table:

CREATE TABLE `log`
(
    `what` enum('add', 'edit', 'remove') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    `with` int(10) unsigned NOT NULL,

    KEY `with_what` (`with`,`what`)
) ENGINE=InnoDB;

INSERT INTO `log` (`what`, `with`) VALUES
    ('add', 1),
    ('edit', 1),
    ('add', 2),
    ('remove', 2);

As I understand, with_what index must have 2 unique entries on its first with level and 3 unique entries in what "subindex". But MySQL reports 4 unique entries for each level. In other words, number of unique elements for each level is always equal to number of rows in log table.

Is that a bug, a feature or my misunderstanding?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about index