MySQL Forums
Forum List  »  Newbie

Re: Slow retreieve under MyIsam
Posted by: Igor Bagrij
Date: November 19, 2009 04:55AM

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 ]

Options: ReplyQuote


Subject
Written By
Posted
November 16, 2009 11:25AM
November 17, 2009 02:07PM
November 19, 2009 04:58AM
November 19, 2009 10:44PM
November 20, 2009 07:33AM
November 17, 2009 11:19PM
November 18, 2009 04:00AM
November 18, 2009 05:28PM
Re: Slow retreieve under MyIsam
November 19, 2009 04:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.