Re: Slow retreieve under MyIsam
Rick, thank you!
Here are the detailes you asked :
[ code ]
SHOW CREATE TABLE Szallito_fejlec\G
***********
Table: Szallito_fejlec
Create Table: CREATE TABLE `szallito_fejlec` (
`rkod` decimal(9,0) NOT NULL,
`Telephely_kod` smallint(6) NOT NULL,
`Szallito_sorszam` int(11) NOT NULL,
`Szamlaszam` int(11) NOT NULL,
`Partner_kod` int(11) NOT NULL,
`Osszertek` double NOT NULL,
`Ossz_afa` double NOT NULL,
`Datum` date NOT NULL,
`Elszamolasi_hatarido` date NOT NULL,
`Fizetesi_mod` varchar(1) CHARACTER SET latin1 NOT NULL,
`Partner_nev` varchar(50) CHARACTER SET latin1 NOT NULL,
`Iranyitoszam` varchar(8) CHARACTER SET latin1 NOT NULL,
`Varosnev` varchar(30) CHARACTER SET latin1 NOT NULL,
`Utca` varchar(30) CHARACTER SET latin1 NOT NULL,
`Szallitasi_kod` smallint(6) NOT NULL,
`Szallitasi_varosnev` varchar(30) CHARACTER SET latin1 NOT NULL,
`Szallitasi_utca` varchar(30) CHARACTER SET latin1 NOT NULL,
`Eladasi_csatorna` varchar(5) CHARACTER SET latin1 NOT NULL,
`Storno_szam` int(11) NOT NULL,
`Ceglanc` smallint(6) NOT NULL,
`Uzletkoto_kod` int(11) NOT NULL,
`Bekerito_kod` int(11) NOT NULL,
`Keszito` smallint(6) NOT NULL,
`Osszekeszito` smallint(6) NOT NULL,
`Rendeles_szam` int(11) NOT NULL,
`Raktari_statusz` varchar(1) CHARACTER SET latin1 NOT NULL,
`Eddig_fizetve` double NOT NULL,
`Eddig_hany_reszlet` smallint(6) NOT NULL,
`Utolso_befiz_datuma` date NOT NULL,
`Utolso_befiz_osszeg` double NOT NULL,
`Utolso_befiz_sorsz` int(11) NOT NULL,
`Utolso_fizetes_TEMP` double NOT NULL,
`Selected` bit(1) NOT NULL,
`Most_befiz` bit(1) NOT NULL,
`Diskre_rakva_hanyszo` smallint(6) NOT NULL,
`Diskre_rakva_utolso` date NOT NULL,
`Utolag_osszekotve` smallint(6) NOT NULL,
`Szamlaszam_Export` int(11) NOT NULL,
`Rendeles_modja` varchar(1) CHARACTER SET latin1 NOT NULL,
`Bizonylat_tipus` varchar(1) CHARACTER SET latin1 NOT NULL,
`Elado` smallint(6) NOT NULL,
`Ellenor` smallint(6) NOT NULL,
`Belfoldi_deviza_szlm` int(11) NOT NULL,
`Visszaru_tipusai_Sch` varchar(2) CHARACTER SET latin1 NOT NULL,
`Sofor` smallint(6) NOT NULL,
`Hivatkozasi_szam` varchar(15) CHARACTER SET latin1 NOT NULL,
`NyugtaSzam` int(11) NOT NULL,
`Szallitasi_irsz` varchar(8) CHARACTER SET latin1 NOT NULL,
`Vevokartya_szama` varchar(15) CHARACTER SET latin1 NOT NULL,
`Turaszam` varchar(5) CHARACTER SET latin1 NOT NULL,
`Beszerzesi_ar_Szumm` double NOT NULL,
`Dobozszam` varchar(15) CHARACTER SET latin1 NOT NULL,
`TevaX_elkuldte` bit(1) NOT NULL,
`Hanyadik_nyomtatas` smallint(6) NOT NULL,
`KULSO_RENDSZ_ATV` varchar(1) CHARACTER SET latin1 NOT NULL,
`KEREK_NYUGT_ESET` smallint(6) NOT NULL,
PRIMARY KEY (`rkod`),
KEY `Sorszam` (`Szallito_sorszam`),
KEY `Partner_Datum` (`Partner_kod`,`Datum`),
KEY `Datum` (`Datum`,`Szallito_sorszam`),
KEY `Uzletkoto` (`Uzletkoto_kod`,`Datum`),
KEY `Nev` (`Partner_nev`,`Datum`),
KEY `Szamlaszam` (`Szamlaszam`),
KEY `PartnerElszDat` (`Partner_kod`,`Elszamolasi_hatarido`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci
Status:
*******
Name: szallito_fejlec
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2237001
Avg_row_length: 109
Data_length: 244414168
Max_data_length: 281474976710655
Index_length: 197678080
Data_free: 0
Auto_increment: NULL
Create_time: 2009-11-17 10:27:16
Update_time: 2009-11-17 10:30:51
Check_time: 2009-11-17 10:30:49
Collation: latin2_hungarian_ci
Checksum: NULL
Create_options:
Comment:
SHOW CREATE TABLE Szallito_tetel\G
***********
Table: Szallito_tetel
Create Table: CREATE TABLE `szallito_tetel` (
`rkod` decimal(9,0) NOT NULL,
`Telephely_kod` smallint(6) NOT NULL,
`Szallito_sorszam` int(11) NOT NULL,
`Termek_kod` varchar(15) CHARACTER SET latin1 NOT NULL,
`Raktar_ID` smallint(6) NOT NULL,
`Szallitasi_darab` double NOT NULL,
`Ar` double NOT NULL,
`Kedvezmeny` double NOT NULL,
`Bonus_van` bit(1) NOT NULL,
`Afa` smallint(6) NOT NULL,
`Mennyisegi_egyseg` varchar(3) CHARACTER SET latin1 NOT NULL,
`Vamtarifa_Szam_Szaml` varchar(10) CHARACTER SET latin1 NOT NULL,
`Partner` int(11) NOT NULL,
`Megnevezes` varchar(30) CHARACTER SET latin1 NOT NULL,
`Record_ID` smallint(6) NOT NULL,
`Termekdij` double NOT NULL,
`Engedmeny_1` double NOT NULL,
`Engedmeny_2` double NOT NULL,
`Engedmeny_3` double NOT NULL,
`Darab` double NOT NULL,
`Rendeles_ID` int(11) NOT NULL,
`Nemzetkozi_ar_DOS` double NOT NULL,
`Nemzetkozi_deviza` varchar(3) CHARACTER SET latin1 NOT NULL,
`Beszar_kod` int(11) NOT NULL,
`Kezi_ar_beiras` bit(1) NOT NULL,
`Beszerzesi_ar` double NOT NULL,
`VER_AD_ID` int(11) NOT NULL,
`AFA_KOD` varchar(2) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`rkod`),
KEY `Sorszam_Term` (`Szallito_sorszam`,`Termek_kod`,`Raktar_ID`,`Ar`,`Kedvezmeny`),
KEY `Termek_Partner` (`Termek_kod`,`Partner`),
KEY `Sorszam_RecID` (`Szallito_sorszam`,`Record_ID`),
KEY `Torles` (`Telephely_kod`,`Szallito_sorszam`,`Record_ID`),
KEY `Tetel_Termekkod` (`Termek_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci
Status:
*******
Name: szallito_tetel
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 5530439
Avg_row_length: 92
Data_length: 512313736
Max_data_length: 281474976710655
Index_length: 439360512
Data_free: 0
Auto_increment: NULL
Create_time: 2009-11-18 12:18:17
Update_time: 2009-11-18 12:21:28
Check_time: 2009-11-18 12:21:28
Collation: latin2_hungarian_ci
Checksum: NULL
Create_options:
Comment:
SHOW CREATE TABLE Termek_torzs\G
***********
Table: Termek_torzs
Create Table: CREATE TABLE `termek_torzs` (
`rkod` decimal(9,0) NOT NULL,
`Telephely_kod` smallint(6) NOT NULL,
`Termek_Kod` varchar(15) CHARACTER SET latin1 NOT NULL,
`Termek_Nev_Eladasi` varchar(30) CHARACTER SET latin1 NOT NULL,
`Arlista_nev` varchar(30) CHARACTER SET latin1 NOT NULL,
`Nagybetus_nev` varchar(30) CHARACTER SET latin1 NOT NULL,
`Utolso_Ft_beszerz_ar` double NOT NULL,
`Akt_Ft_lista_ar` double NOT NULL,
`Akt_Ft_bolti_ar` double NOT NULL,
`Szallitoi_ar` double NOT NULL,
`Deviza_Nem` varchar(3) CHARACTER SET latin1 NOT NULL,
`Szallitoi_kedvezmeny` varchar(5) CHARACTER SET latin1 NOT NULL,
`Bonus_Kod` varchar(5) CHARACTER SET latin1 NOT NULL,
`Nemzetkozi_ar` double NOT NULL,
`Nemzetkozi_ar_deviza` varchar(3) CHARACTER SET latin1 NOT NULL,
`Vamtarifa_Kod` varchar(8) CHARACTER SET latin1 NOT NULL,
`Vamtarifa_szam_szaml` varchar(10) CHARACTER SET latin1 NOT NULL,
`Afa` smallint(6) NOT NULL,
`Jogdij_kategoria` varchar(5) CHARACTER SET latin1 NOT NULL,
`Szallitasi_kategoria` varchar(5) CHARACTER SET latin1 NOT NULL,
`Brutto_Suly` double NOT NULL,
`Netto_Suly` double NOT NULL,
`Hossz` double NOT NULL,
`Helyettesito_Tipus` varchar(15) CHARACTER SET latin1 NOT NULL,
`Minimalis_keszlet` double NOT NULL,
`Maximalis_keszlet` double NOT NULL,
`Megjegyzes` mediumtext CHARACTER SET latin1 NOT NULL,
`TermekSzolgaltatas` bit(1) NOT NULL,
`Maximalis_kedvezmeny` double NOT NULL,
`Mennyisegi_egyseg` varchar(3) CHARACTER SET latin1 NOT NULL,
`Termek_Statusz` varchar(1) CHARACTER SET latin1 NOT NULL,
`Kezdet_datuma` date NOT NULL,
`Megszunes_datuma` date NOT NULL,
`Gyujtokod` varchar(15) CHARACTER SET latin1 NOT NULL,
`VonalKod` varchar(15) CHARACTER SET latin1 NOT NULL,
`ArCsoport` varchar(10) CHARACTER SET latin1 NOT NULL,
`Termek_tipus` varchar(5) CHARACTER SET latin1 NOT NULL,
`Termek_tipus_2` varchar(5) CHARACTER SET latin1 NOT NULL,
`Rendelesi_egyseg` int(11) NOT NULL,
`Napi_atlag` double NOT NULL,
`Gyariszamos` bit(1) NOT NULL,
`Beszallito_cikkszama` varchar(15) CHARACTER SET latin1 NOT NULL,
`Termek_Nev_Beszerz` varchar(160) CHARACTER SET latin1 NOT NULL,
`Beszallito_kodja` int(11) NOT NULL,
`Szarmazasi_orszag` varchar(3) CHARACTER SET latin1 NOT NULL,
`Vamkezelesi_dij` varchar(5) CHARACTER SET latin1 NOT NULL,
`Stat_illetek` varchar(5) CHARACTER SET latin1 NOT NULL,
`Biztositas_alapeset` varchar(5) CHARACTER SET latin1 NOT NULL,
`Fizetesi_mod_vam` varchar(5) CHARACTER SET latin1 NOT NULL,
`Egyeb_koltseg` double NOT NULL,
`Egyeb_koltseg_Ft` double NOT NULL,
`Fuvar_Paritas_kod` varchar(3) CHARACTER SET latin1 NOT NULL,
`Raktari_hely` varchar(5) CHARACTER SET latin1 NOT NULL,
`Jovedeki_engedelyes` bit(1) NOT NULL,
`Viszonyszam` double NOT NULL,
`Nev_masodik_sora` varchar(40) CHARACTER SET latin1 NOT NULL,
`Nev_harmadik_sora` varchar(40) CHARACTER SET latin1 NOT NULL,
`Termekdij` double NOT NULL,
`Pontertek` int(11) NOT NULL,
`Listar_atarazas_datu` date NOT NULL,
`Viszonyszamliter` double NOT NULL,
`Webes_kategoria` varchar(20) CHARACTER SET latin1 NOT NULL,
`Ar_korrekcio` double NOT NULL,
`het_szallito_fogyas1` int(11) NOT NULL,
`het_szallito_fogyas2` int(11) NOT NULL,
`ho_szallito_fogyas1` int(11) NOT NULL,
`ev_szallito_fogyas14` int(11) NOT NULL,
`ev_szallito_fogyas12` int(11) NOT NULL,
`ev_szallito_fogyas1` int(11) NOT NULL,
`het_szamla_fogyas1` int(11) NOT NULL,
`het_szamla_fogyas2` int(11) NOT NULL,
`ho_szamla_fogyas1` int(11) NOT NULL,
`ev_szamla_fogyas14` int(11) NOT NULL,
`ev_szamla_fogyas12` int(11) NOT NULL,
`ev_szamla_fogyas1` int(11) NOT NULL,
`RaktarKeszlet_1` double NOT NULL,
`RaktarKeszlet_2` double NOT NULL,
`RaktarSzamlas_1` double NOT NULL,
`RaktarSzamlas_2` double NOT NULL,
`Fokonyvi_szamlaszam1` varchar(8) CHARACTER SET latin1 NOT NULL,
`Fokonyvi_szamlaszam2` varchar(8) CHARACTER SET latin1 NOT NULL,
`Balance_old` smallint(6) NOT NULL,
`Terfogat` double NOT NULL,
`SARS_szamos` bit(1) NOT NULL,
`Fontos_megjegyzes` varchar(60) CHARACTER SET latin1 NOT NULL,
`Kisker_arucimken_me` varchar(3) CHARACTER SET latin1 NOT NULL,
`Kisker_arucimkehez_o` double NOT NULL,
`Min_arres_figyeles_t` bit(1) NOT NULL,
`Lapszam_szigoru_szam` smallint(6) NOT NULL,
`Kell_hozza_vonalkodo` varchar(1) CHARACTER SET latin1 NOT NULL,
`WEB_aruhaz_kep_fajl` varchar(15) CHARACTER SET latin1 NOT NULL,
`Gyarto_Internetes_ol` varchar(50) CHARACTER SET latin1 NOT NULL,
`Beszar_kod` int(11) NOT NULL,
`Szezon_tipus` varchar(5) CHARACTER SET latin1 NOT NULL,
`Tovabbadott_kedvezm` double NOT NULL,
`Balance` int(11) NOT NULL,
`ELOZO_TERM_STATUSZ` varchar(1) CHARACTER SET latin1 NOT NULL,
`AFA_KOD` varchar(2) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`rkod`),
KEY `Termek_kod` (`Termek_Kod`),
KEY `Eladasi_nev` (`Termek_Nev_Eladasi`,`Nev_masodik_sora`,`Nev_harmadik_sora`,`Termek_Kod`),
KEY `Tipus` (`Termek_tipus`,`Termek_Kod`),
KEY `Vonalkod` (`VonalKod`),
KEY `Nagybetus_nev` (`Nagybetus_nev`,`Nev_masodik_sora`,`Nev_harmadik_sora`,`Termek_Kod`),
KEY `Beszallito_cikk` (`Beszallito_cikkszama`),
KEY `Raktari_hely` (`Raktari_hely`,`Termek_Kod`),
KEY `Beszallito` (`Beszallito_kodja`,`Termek_Kod`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci
Status:
*******
Name: termek_torzs
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 39106
Avg_row_length: 339
Data_length: 13270696
Max_data_length: 281474976710655
Index_length: 6379520
Data_free: 0
Auto_increment: NULL
Create_time: 2009-11-17 10:24:50
Update_time: 2009-11-17 10:24:59
Check_time: 2009-11-17 10:25:01
Collation: latin2_hungarian_ci
Checksum: NULL
Create_options:
Comment:
mysql> EXPLAIN SELECT st.Telephely_kod, sf.Uzletkoto_kod,
-> Year(sf.Datum) AS `EV`,
-> Month(sf.Datum) AS `HO`,
-> SUM(st.Szallitasi_darab* st.Ar*(1- st.Kedvezmeny/100)) AS `ERTEK`,
-> tt.Termek_tipus
-> FROM szallito_tetel AS st
-> JOIN szallito_fejlec AS sf ON (st.Szallito_sorszam = sf.Szallito_sorszam)
-> JOIN termek_torzs AS tt ON (st.Termek_kod = tt.Termek_Kod)
-> GROUP BY st.Telephely_kod,
-> sf.Uzletkoto_kod, Year(sf.Datum),
-> Month(sf.Datum), tt.Termek_tipus\G
1. row ****
id: 1
select_type: SIMPLE
table: st
type: ALL
possible_keys: Sorszam_Term,Termek_Partner,Sorszam_RecID,Tetel_Termekkod
key: NULL
key_len: NULL
ref: NULL
rows: 5530439
Extra: Using temporary; Using filesort
2. row ****
id: 1
select_type: SIMPLE
table: sf
type: ref
possible_keys: Sorszam
key: Sorszam
key_len: 4
ref: mx_reportwizardhoz.st.Szallito_sorszam
rows: 1
Extra:
3. row ****
id: 1
select_type: SIMPLE
table: tt
type: ref
possible_keys: Termek_kod
key: Termek_kod
key_len: 17
ref: mx_reportwizardhoz.st.Termek_kod
rows: 1
Extra:
3 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%buffer%' \g
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 49283072 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 134217728 |
| myisam_sort_buffer_size | 268435456 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
12 rows in set (0.00 sec)
[ /code ]