1

I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct count returns 315. And then I have realized that there are 3 other such indexes (equal to tables's row size), and all their distinct counts are way below. So, what could be the reason these indexes' carnalities are equal to table's row size?

Note: There are 97 columns and 27 indexes.

The table:

CREATE TABLE `isemri` (
  `RECNO` int(12) NOT NULL AUTO_INCREMENT,
  `MUSTERI_RECNO` int(11) NOT NULL,
  `MUSTERI_PER_RECNO` int(11) DEFAULT NULL,
  `YIL` smallint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `ISEMRI_NO` int(6) NOT NULL DEFAULT '0',
  `BOLGE` int(6) unsigned NOT NULL,
  `URUN_BARKOD` varchar(13) NOT NULL DEFAULT '''''',
  `ISTEK_TIPI` tinyint(1) DEFAULT '0',
  `MUSTERI_NO` bigint(20) DEFAULT NULL,
  `SERVIS_CAGRI_TAR` datetime DEFAULT NULL,
  `MUSTERI_PER` varchar(30) DEFAULT NULL,
  `SERVIS_BAS_TAR` datetime DEFAULT NULL,
  `SERVIS_BIT_TAR` datetime DEFAULT NULL,
  `ISTEGI_ALAN` varchar(15) DEFAULT NULL,
  `URUN_CIHAZ` smallint(6) DEFAULT NULL,
  `URUN_CIHAZ_ADI` varchar(40) DEFAULT NULL,
  `URUN_MARKA` smallint(6) DEFAULT NULL,
  `URUN_MARKA_ADI` varchar(20) DEFAULT NULL,
  `URUN_MODEL` varchar(35) DEFAULT NULL,
  `URUN_MODEL_ADI` varchar(150) DEFAULT NULL,
  `URUN_SERI_NO` varchar(100) DEFAULT NULL,
  `SERVIS_VEREN` varchar(50) DEFAULT NULL,
  `SERVIS_TURU` tinyint(2) NOT NULL DEFAULT '1',
  `ARIZA_ACIKLAMASI` mediumtext,
  `ARIZASERVIS_ACIKLAMA` varchar(250) DEFAULT NULL,
  `ACILIYET_DERECESI` tinyint(2) NOT NULL DEFAULT '2',
  `SERVIS_DURUMU` tinyint(2) NOT NULL DEFAULT '1',
  `GERI_BILDIRIM` tinyint(1) NOT NULL DEFAULT '0',
  `KON_GEL_DON_TAR` datetime DEFAULT NULL,
  `GERI_BILDIRIM_TARIHI` datetime DEFAULT NULL,
  `TAHMINI_TESLIM_TARIHI` datetime DEFAULT NULL,
  `YAPILANLAR` varchar(255) DEFAULT NULL,
  `YAPILANLAR_M` mediumtext,
  `SERVIS_YERI` varchar(20) DEFAULT NULL,
  `TOPLAM` float(12,2) DEFAULT NULL,
  `KDV_TOP` float(12,2) DEFAULT NULL,
  `GENEL_TOPLAM` float(12,2) DEFAULT NULL,
  `INSERT_USER` varchar(15) DEFAULT NULL,
  `GIRIS_KARGO_NO` varchar(10) DEFAULT NULL,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) DEFAULT NULL,
  `UPDATE_DATE` datetime DEFAULT NULL,
  `BARKOD` int(6) NOT NULL DEFAULT '0',
  `SERVIS_BITIS` tinyint(1) DEFAULT '0',
  `GARANTI_BELGENO` varchar(12) DEFAULT NULL,
  `GARANTI_TARIHI` date DEFAULT NULL,
  `EL_ISEMRI_NO` int(11) DEFAULT '0',
  `ONAY_TARIHI` datetime DEFAULT NULL,
  `ONAYLAYAN` varchar(30) DEFAULT NULL,
  `ALAN1` varchar(20) DEFAULT NULL,
  `ALAN2` varchar(20) DEFAULT NULL,
  `ALAN3` varchar(20) DEFAULT NULL,
  `ALAN4` varchar(20) DEFAULT NULL,
  `DOLAR_KURU` float DEFAULT '2',
  `EURO_KURU` float DEFAULT '2',
  `KDV` tinyint(1) DEFAULT '0',
  `BILGILENDIR` tinyint(1) DEFAULT '0',
  `TESLIM_SEKLI` varchar(10) DEFAULT NULL,
  `TESLIM_NOTU` varchar(150) DEFAULT NULL,
  `ARIZA_TIP_NO` smallint(6) DEFAULT NULL,
  `ARIZASERVIS_TIP` smallint(6) DEFAULT NULL,
  `GETIREN_KARGO_FRM` varchar(50) DEFAULT NULL,
  `SERV_ALAN1` varchar(25) DEFAULT NULL,
  `SERV_ALAN2` varchar(25) DEFAULT NULL,
  `DOSYA__` varchar(250) DEFAULT NULL,
  `HAKEDIS_DURUM` tinyint(1) unsigned DEFAULT '0',
  `HAKEDIS_FATURANO` int(11) DEFAULT NULL,
  `HAKEDIS_FATURA_DURUM` int(1) DEFAULT '0',
  `ONAY_DURUM` tinyint(1) unsigned DEFAULT '0',
  `MONTAJ_KODU` int(6) DEFAULT NULL,
  `urun_id` int(11) DEFAULT NULL,
  `gondKargoFirmasi` tinyint(2) DEFAULT NULL,
  `gondKargoNo` int(11) DEFAULT NULL,
  `gondIrsNo` varchar(30) DEFAULT NULL,
  `gondGelTar` date DEFAULT NULL,
  `BAYI_KODU` varchar(50) DEFAULT '0',
  `HAKEDIS_TARIHI` datetime DEFAULT NULL,
  `HAKEDIS_ONAY_PERSONEL` varchar(30) DEFAULT NULL,
  `HURDA_CTRL` tinyint(2) DEFAULT '0',
  `DOSYA` tinyint(2) DEFAULT '0',
  `RANDEVU_TARIHI` datetime DEFAULT NULL,
  `MUSTERI_ACIKLAMA` varchar(200) DEFAULT NULL,
  `PLASIYER` varchar(50) DEFAULT NULL,
  `FIRMA_SERINO` varchar(20) DEFAULT NULL,
  `MONTAJ_KART_NO` varchar(50) DEFAULT NULL,
  `SERINO2` varchar(50) DEFAULT NULL,
  `SATICI_FIRMA` varchar(50) DEFAULT NULL,
  `URUN_DEGISIM_DURUMU` int(11) DEFAULT NULL,
  `URUN_HASAR_DURUMU` int(6) DEFAULT NULL,
  `BOLGE_2` varchar(12) DEFAULT NULL,
  `KILIT` int(2) DEFAULT NULL,
  `ON_ONAY` int(11) DEFAULT NULL,
  `RANDEVU_BITIS_TARIHI` datetime DEFAULT NULL COMMENT 'iş emri randavü bitiş tarihi',
  `URUN_KALITE` tinyint(4) DEFAULT NULL,
  `BOLGE_X` varchar(255) DEFAULT NULL,
  `REWORK` enum('0','1') DEFAULT '0' COMMENT 'Rework olup olmadığını gösteriyor.',
  PRIMARY KEY (`RECNO`),
  KEY `MUSTERI_NO` (`MUSTERI_NO`) USING BTREE,
  KEY `EL_ISEMRI_NO` (`EL_ISEMRI_NO`) USING BTREE,
  KEY `MUSTERI_RECNO` (`MUSTERI_RECNO`) USING BTREE,
  KEY `ISEMRI_NO` (`ISEMRI_NO`) USING BTREE,
  KEY `ALAN1` (`ALAN1`) USING BTREE,
  KEY `URUN_SERI_NO` (`URUN_SERI_NO`) USING BTREE,
  KEY `SERVIS_TURU` (`SERVIS_TURU`) USING BTREE,
  KEY `SERVIS_DURUMU` (`SERVIS_DURUMU`) USING BTREE,
  KEY `ISTEK_TIPI` (`ISTEK_TIPI`) USING BTREE,
  KEY `urun_id` (`urun_id`) USING BTREE,
  KEY `URUN_CIHAZ` (`URUN_CIHAZ`) USING BTREE,
  KEY `HAKEDIS_DURUM` (`HAKEDIS_DURUM`) USING BTREE,
  KEY `HAKEDIS_TARIHI` (`HAKEDIS_TARIHI`) USING BTREE,
  KEY `HAKEDIS_ONAY_PERSONEL` (`HAKEDIS_ONAY_PERSONEL`) USING BTREE,
  KEY `SERVIS_BAS_TAR` (`SERVIS_BAS_TAR`) USING BTREE,
  KEY `BOLGE_2` (`BOLGE_2`) USING BTREE,
  KEY `URUN_MARKA` (`URUN_MARKA`) USING BTREE,
  KEY `URUN_MODEL` (`URUN_MODEL`) USING BTREE,
  KEY `MUSTERI_PER` (`MUSTERI_PER`) USING BTREE,
  KEY `MONTAJ_KODU` (`MONTAJ_KODU`) USING BTREE,
  KEY `BOLGE` (`BOLGE`),
  KEY `HAKEDIS_FATURANO` (`HAKEDIS_FATURANO`),
  KEY `REWORK` (`REWORK`) USING BTREE,
  KEY `SERVIS_BITIS` (`SERVIS_BITIS`) USING BTREE,
  KEY `HAKEDIS_FATURA_DURUM` (`HAKEDIS_FATURA_DURUM`),
  KEY `MUSTERI_PER_RECNO` (`MUSTERI_PER_RECNO`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1335476 DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=DYNAMIC

SHOW TABLE STATUS:

Name: isemri
Engine: MyISAM
Version: 10
Rpw_format: Dynamic
Rows: 1328150
Avg_row_length: 459
Data_length: 610624628
Max_data_length: 281474976710655
Index_length: 420404224
Data_free: 0
Auto_icrement: 1335384
Create_time: 2016-02-12 16:22:12
Update_time: 2016-02-13 10:43:59
Check_time: 2016-02-12 17:52:23
Collation: utf8_general_ci
Checksum: 
Create_options: pack_keys=0 row_format=DYNAMIC
Comment: 

Distinct count:

URUN_SERI_NO: 1012754
SERVIS_BAS_TAR: 1300066
BOLGE_2: 5
MUSTERI_PER_RECNO: 319

indexes

  • Try running analyze table <tablename>; and check the cardinality again. – jkavalik Feb 12 '16 at 19:46
  • @jkavalik, I did it few times. Didn't help. – ilhan Feb 12 '16 at 23:17
  • 27 indexes? Bad news. Are any of them 'composite'? – Rick James Feb 13 '16 at 5:29
  • Hmmm... What is the "carnality" of an index? – Rick James Feb 13 '16 at 5:29
  • @RickJames, there is no composite index among the indexes. – ilhan Feb 13 '16 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.