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.
