Bug #76117 Index cardinality not being updated when the data is changed
Submitted: 3 Mar 2015 6:56 Modified: 4 Mar 2015 7:40
Reporter: Raghu reddy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.6.16-log OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: mysql indexes

[3 Mar 2015 6:56] Raghu reddy
Description:
Hi Mysql experts,

I am using mysql 5.6.16-log version and have an issue on performance.
I have a table "history" and which contains 1.2 million records.

SHOW CREATE TABLE  `history`;

CREATE TABLE `history` (
  `sno` BIGINT(20) DEFAULT NULL,
  `price_price` DECIMAL(10,4) DEFAULT NULL,
  `price_closeprice` DECIMAL(10,4) DEFAULT NULL,
  `price_closingDate` DATETIME DEFAULT NULL,
  `price_dateCreated` TIMESTAMP NULL DEFAULT NULL,
  `price_isCurrent` TINYINT(1) DEFAULT NULL,
  `company_asxCode` CHAR(10) DEFAULT NULL,
  `company_id` BIGINT(20) DEFAULT NULL,
  KEY `idx_pricedate` (`price_dateCreated`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

SELECT COUNT(*) FROM  `history` ;
total records:1200000

SELECT COUNT(DISTINCT `sno`) FROM `history`;
unique records:1200000

CREATE INDEX idx_sno ON history(sno);
SHOW INDEXES FROM history;
cardinality:1194471

START TRANSACTION;
UPDATE history SET sno=25 WHERE sno < 300000;
COMMIT;

SHOW INDEXES FROM history;
cardinality:1194471

DROP INDEX idx_sno ON history;
CREATE INDEX idx_sno ON history(sno);

SHOW INDEXES FROM history;
cardinality:1194471

but when I checked the unique records for column "sno"

SELECT COUNT(DISTINCT `sno`) FROM `history`;
unique records:900002

My question is if the column "sno" is having 900002 unique records then why cardinality showing 1194471.
If the cardinality is not being updated when the data is changed then queries will degrade the performance.

How to repeat:
Create a table history and check the number of records,unique records and also cardinality.
Then update some records as duplicates for indexed column "sno" and then again check cardinality.

Suggested fix:
Cardinality must be updated when the index column is changed.
[4 Mar 2015 7:40] Chiranjeevi Battula
Hello Raghu reddy,

Thank you for the bug report.
This is duplicate of Bug #58382.

For more info see bug#58382.

Thanks,
Chiranjeevi.