Why is MySQL multi-column index overpopulated?

Question

Consider the 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 it, an index with_whatmust have two unique entries in its first level withand 3 (EDIT: 4) unique entries in the what"subindex". But MySQL reports 4 unique records for each level. In other words, the number of unique elements for each level is always equal to the number of rows in the table log.

EDIT: For the "second level" it is normal that the number of unique records is equal to the total number of records, but not suitable for the top level.

EDIT2: , , with, , , int (11) int (10), . EXPLAIN SELECT COUNT(DISTINCT 'with') FROM log rows.

, ?

Answer 1

SHOW INDEXES .

, , , , ANALYZE TABLE log.

ANALYZE, .

Answer 2

. , . MySQL 4 , 4 .

,

KEY `with_what` (`with`,`what`)

KEY `with` (`with`),
KEY `what` (`what`)

, .

Answer 3

"multi column" " ", . , , .

, , : http://Use-The-Index-Luke.com/