Description:
We have an OLAP application currently runs on MSSQL. The applications main purpose is to generate profit reports about the clients of a bank. Monthly updates takes too much time and I wanted to test the stability and speed of MySQL vs. MSSQL.
The table creation script (direct port from MSSQL):
CREATE TABLE QUANTIS_MKR (
SUBE decimal(4, 0) NOT NULL ,
MUSTERI_NO decimal(8, 0) NOT NULL ,
MKR_AY decimal(2, 0) NOT NULL ,
GRUP_KODU decimal(5, 0) NOT NULL ,
SEKTOR_KODU char (2) ,
MUSTERI_ADI char (15) ,
AKTIF_KODU char (1) ,
PERSONEL_KODU char (1) ,
MUST_TEMSILCISI decimal(5, 0) NOT NULL ,
TL_KREDI decimal(9, 0) NOT NULL ,
YP_KREDI decimal(9, 0) NOT NULL ,
CM_KREDI decimal(9, 0) NOT NULL ,
KSVD_KREDI decimal(9, 0) NOT NULL ,
ORVD_KREDI decimal(9, 0) NOT NULL ,
PRB_KREDI decimal(9, 0) NOT NULL ,
TCMB_REES decimal(9, 0) NOT NULL ,
EXIM decimal(9, 0) NOT NULL ,
KASA_KOLAYLIGI decimal(9, 0) NOT NULL ,
DVZ_END_KREDI decimal(9, 0) NOT NULL ,
TL_VADELI decimal(9, 0) NOT NULL ,
TL_VADESIZ decimal(9, 0) NOT NULL ,
YP_VADELI decimal(9, 0) NOT NULL ,
YP_VADESIZ decimal(9, 0) NOT NULL ,
YP_SWAP decimal(9, 0) NOT NULL ,
GNK_RISK decimal(9, 0) NOT NULL ,
KOMISYON decimal(9, 0) NOT NULL ,
KAMBIYO decimal(9, 0) NOT NULL ,
EMLAK_KREDISI decimal(9, 0) NOT NULL ,
OTOMOBIL_KREDISI decimal(9, 0) NOT NULL ,
TAKAS_CIK_BNK_CEK decimal(9, 0) NOT NULL ,
TUKETICI_KREDISI decimal(9, 0) NOT NULL ,
DIGER_GELIR decimal(9, 0) NOT NULL ,
MASRAF decimal(9, 0) NOT NULL ,
TL_MAL_KAY decimal(9, 0) NOT NULL ,
YP_MAL_KAY decimal(9, 0) NOT NULL ,
TL_NETFAIZ decimal(9, 0) NOT NULL ,
YP_NETFAIZ decimal(9, 0) NOT NULL ,
TOPLAM_NETFAIZ decimal(9, 0) NOT NULL ,
MUSTERI_KARI decimal(18, 0) NOT NULL ,
KAR_YUZDESI decimal(7, 2) NOT NULL ,
IT_KKVM_DOS decimal(9, 0) NOT NULL ,
IT_KKVM_TRS decimal(9, 0) NOT NULL ,
IT_KKVM_AVAL_DOS decimal(9, 0) NOT NULL ,
IT_KKVM_AVAL_TRS decimal(9, 0) NOT NULL ,
IT_VM_DOS decimal(9, 0) NOT NULL ,
IT_VM_TRS decimal(9, 0) NOT NULL ,
IT_KKMM_DOS decimal(9, 0) NOT NULL ,
IT_KKMM_TRS decimal(9, 0) NOT NULL ,
IT_KKMM_AVAL_DOS decimal(9, 0) NOT NULL ,
IT_KKMM_AVAL_TRS decimal(9, 0) NOT NULL ,
IT_MM_DOS decimal(9, 0) NOT NULL ,
IT_MM_TRS decimal(9, 0) NOT NULL ,
IT_KKLC_DOS decimal(9, 0) NOT NULL ,
IT_KKLC_TRS decimal(9, 0) NOT NULL ,
IT_VADELI_LC_DOS decimal(9, 0) NOT NULL ,
IT_VADELI_LC_TRS decimal(9, 0) NOT NULL ,
IT_SIGHT_LC_DOS decimal(9, 0) NOT NULL ,
IT_SIGHT_LC_TRS decimal(9, 0) NOT NULL ,
IH_VM_DOS decimal(9, 0) NOT NULL ,
IH_VM_TRS decimal(9, 0) NOT NULL ,
IH_MM_DOS decimal(18, 0) NOT NULL ,
IH_MM_TRS decimal(9, 0) NOT NULL ,
IH_PESIN_ODEME_DOS decimal(9, 0) NOT NULL ,
IH_PESIN_ODEME_TRS decimal(9, 0) NOT NULL ,
IH_LC_DOS decimal(9, 0) NOT NULL ,
IH_LC_TRS decimal(9, 0) NOT NULL ,
IH_TEYITLI_LC_DOS decimal(9, 0) NOT NULL ,
IH_TEYITLI_LC_TRS decimal(9, 0) NOT NULL ,
GK_CIKTI decimal(9, 0) NOT NULL ,
GK_GIRDI decimal(9, 0) NOT NULL ,
YP_KISA_VADELI decimal(9, 0) NOT NULL ,
YP_ORTA_UZUN_VADELI decimal(9, 0) NOT NULL ,
YP_EXIM decimal(9, 0) NOT NULL ,
YP_DEK decimal(9, 0) NOT NULL ,
TL_TEMINAT decimal(9, 0) NOT NULL ,
YP_TEMINAT decimal(9, 0) NOT NULL ,
YP_VADELI_USD decimal(9, 0) NOT NULL ,
YP_VADESIZ_USD decimal(9, 0) NOT NULL ,
TL_BLOKE decimal(9, 0) NOT NULL ,
YP_BLOKE decimal(9, 0) NOT NULL ,
COLLECTION decimal(9, 0) NOT NULL ,
DISBURSEMENT decimal(9, 0) NOT NULL ,
FAALIYET decimal(3, 0) NOT NULL ,
RATING decimal(1, 0) NOT NULL ,
IT_PESIN_ODEME_DOS decimal(9, 0) NOT NULL ,
IT_PESIN_ODEME_TRS decimal(9, 0) NOT NULL ,
DEK_EMLAK decimal(9, 0) NOT NULL ,
DEK_OTOMOBIL decimal(9, 0) NOT NULL ,
DEK_TUKETICI decimal(9, 0) NOT NULL ,
MASRAF_KRDP decimal(9, 0) NOT NULL ,
DIREKT_SATIS_TEMSILCISI decimal(5, 0) NOT NULL ,
LC_RISK decimal(9, 0) NOT NULL ,
KABUL_AVAL_RISK decimal(9, 0) NOT NULL ,
YP_TEM_MEK_RISK decimal(9, 0) NOT NULL ,
OZEL_HESAP_KREDISI decimal(9, 0) NOT NULL ,
KIK_TL_OTO decimal(9, 0) NOT NULL ,
KIK_TL_EMLAK decimal(9, 0) NOT NULL ,
KIK_TL_TUKETICI decimal(9, 0) NOT NULL ,
KIK_DEK_OTO decimal(9, 0) NOT NULL ,
KIK_DEK_EMLAK decimal(9, 0) NOT NULL ,
KIK_DEK_TUKETICI decimal(9, 0) NOT NULL ,
HISSE_SENEDI_KREDISI decimal(9, 0) NOT NULL ,
KREDI_KARTI_HACIM decimal(9, 0) NOT NULL ,
KREDI_KARTI_KAR decimal(9, 0) NOT NULL ,
AKTIF_MUS char (1) ,
AYLIK_BRUT_KAR decimal(9, 0) NOT NULL
);
(Table type is MyISAM)
The main table for reports (QUANTIS_MKR) has more than 100 fields and currently holds 4 million records.
I modified the input file of a month for MySQL LOAD DATA statement. After running LOAD DATA I consistently got "server connection lost" errors. Later I changed the my.ini file with the my-large. With this way I ran LOAD DATA successfully. Then I created the composite index for the first three rows. I got disconnected from the server again. But the index seems to be created. I tried to create index specifically for MUSTERI_NO. I again disconnected, but again index seems to be created. Now anytime I create an index I get a "server connection lost" but the index seems to be created. The same applies for dropping an index too.
How to repeat:
DROP INDEX MUSTERI_IND ON QUANTIS_MKR;
-- or --
CREATE INDEX MUSTERI_IND ON QUANTIS_MKR(MUSTERI_NO);
