CODEWITHSUNDEEP
mysqlindexing
ABC
ABC

Reputation: 4373

Is there any way to see the content of an index mysql?

I have a table like:

| employee | CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `age_idx` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1 |

Here I have created an index called "age_idx", I have 10000 records in this table, Is there any way I can see how the index store the pointers of records?

Upvotes: 0

Views: 44

Answers (2)

Rick James
Rick James

Reputation: 142560

Your table in ENGINE=InnoDB. You have two keys.

PRIMARY KEY(id) is "clustered" with the data in a B+Tree. That is, all the data is arranged in order by id. (See the Wikipedia entry for BTree.)

Each "secondary key" (such as your KEY age_idx (age)) is constructed this way.

  1. Tack on the rest of the PRIMARY KEY columns. Now it is (age, id).
  2. Build a B+Tree with just those columns.

Consequences:

  • Any query needing only age and id can be handled entirely in the secondary index. This is a "covering index" The EXPLAIN gives this clue: Using index.
  • To look up something by age (and not 'covering'), first there is a BTree lookup and scan in the secondary index, then there are repeated lookups by id in the PRIMARY KEY / Data BTree.
  • A "range" scan by the PRIMARY KEY (seems unlikely for id) is very efficient because it is simply accessing consecutive entries in the B+Tree.

Important characteristics of a B+Tree:

  • Very efficient to find one item. O(log(n))
  • Very efficient to scan -- the 'next' and 'prev' item are essentially adjacent in the tree.
  • Rule of Thumb: The fan-out is 100; there are 100 items in a "block". (This can vary from 1 to maybe 1000; 100 is usually "close enough".) An InnoDB block is 16KB.

Your particular fields are excessively large:

  • id BIGINT SIGNED takes 8 bytes. MEDIUMINT UNSIGNED (3 bytes, range 0..16 million) is probably a better choice for "employee".
  • age INT SIGNED takes 4 bytes and allows negative ages and ages past 4 bilion. TINYINT UNSIGNED (1 byte and 0..255) is more than sufficient for anyone living today.

There is a bunch of overhead in any InnoDB BTree; so the byte counts above are probably low by a factor of more than 2 when estimate how much disk space the index (or data) will take.

Upvotes: 0

Norbert
Norbert

Reputation: 6084

SHOW INDEX FROM employee;

And then a lot of reading on cardinality (is high cardinality on your first index value better of worse in case of a btree, etc).

You can not see the actual content or content distribution of the index.

Upvotes: 1

Related Questions