Back To Programming / Scripting / Coding Forum

Mysql -- Index Cardinality

  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post December 2nd, 2006, 10:25 pm

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:
Strong with this one, the sudo is.
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post December 2nd, 2006, 10:25 pm

  • katana
  • Mastermind
  • Mastermind
  • User avatar
  • Joined: Sep 07, 2004
  • Posts: 2390
  • Loc: Edinburgh, Scotland
  • Status: Offline

Post December 3rd, 2006, 2:39 am

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.
Why do geeks get Halloween and Christmas confused?
Because 31 Oct == 25 Dec
www.darren-king.co.uk
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post December 3rd, 2006, 3:00 am

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;
  9. INSERT INTO `test` (`one`, `two`) VALUES
  10. (0, 0),
  11. (1, 0),
  12. (1, 0),
  13. (0, 1),
  14. (1, 1),
  15. (1, 1),
  16. (1, 0),
  17. (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.
Strong with this one, the sudo is.
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post October 21st, 2009, 12:58 pm

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.
Strong with this one, the sudo is.
  • nharkins
  • Born
  • Born
  • No Avatar
  • Joined: Jan 27, 2010
  • Posts: 1
  • Status: Offline

Post January 27th, 2010, 2:08 pm

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

Page 1 of 1

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 125 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
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.