Mysql -- Index Cardinality

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

What the heck is "Cardinality" ?

I've been thinking it's the number of unique row values for a column, but looking at a table with an UNSIGNED TINYINT column with values of only 0 and 1 I have a Cardinality of 3. :scratchhead:
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • katana
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 2391
  • Loc: Edinburgh, Scotland

Post 3+ Months Ago

It is the number of unique values (normally for the index on a table). For a table with a single primary key column, the cardinality should normally be equal to the number of rows in the table.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

Just discovered that cardinality gets adjusted when Tables are Optimized.

It's keeping track of unique rows, but I'm getting single column indexes keeping track of two columns for some reason.

At first this table was showing Cardinality of 8 for both indexes, after optimizing the table the Cardinality was 4 on both indexes.


Code: [ Select ]
CREATE TABLE `test` (
 `one` tinyint(1) unsigned NOT NULL default '0',
 `two` tinyint(1) NOT NULL default '0',
 KEY `two` (`two`),
 KEY `one` (`one`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test` (`one`, `two`) VALUES
(0, 0),
(1, 0),
(1, 0),
(0, 1),
(1, 1),
(1, 1),
(1, 0),
(1, 0);
  1. CREATE TABLE `test` (
  2.  `one` tinyint(1) unsigned NOT NULL default '0',
  3.  `two` tinyint(1) NOT NULL default '0',
  4.  KEY `two` (`two`),
  5.  KEY `one` (`one`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  7. INSERT INTO `test` (`one`, `two`) VALUES
  8. (0, 0),
  9. (1, 0),
  10. (1, 0),
  11. (0, 1),
  12. (1, 1),
  13. (1, 1),
  14. (1, 0),
  15. (1, 0);


Cardinality of 8 right now, but after running this here, it's 4 on both indexes.

Code: [ Select ]
OPTIMIZE TABLE `test`;


I don't understand it, seems the Cardinality should be 2 for both indexes.

Using Server version: 5.0.27-community-nt by the way, also using phpMyAdmin - 2.9.1.1.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

Ha, figures I'd come across my own thread in a search ! :D

From the manual,
Quote:
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.


Key word, estimate.
  • nharkins
  • Born
  • Born
  • nharkins
  • Posts: 1

Post 3+ Months Ago

search for mysql bug 44059.
it's only just been fixed recently.

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 128 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.