Why MySQL multiple-column index is overpopulated?
- by actual
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?
