Slow retreieve under MyIsam
I'm trying MySql for the first time.
We have SQL tool that alows the customer-defined retrieving of data from SQL databases. Usually the SQLs are rather simple, without nested queries, unions, etc.
The most complicated allowed things are the table joins. Our customer has MySQL database (MySQL 5.1.37-community), and is using MySqlClient/v 6.1.2 for .Net.
So, joined query from 3 tables on MyISAM engine
SELECT szallito_tetel.Telephely_kod,
szallito_fejlec.Uzletkoto_kod,
Year(szallito_fejlec.Datum) AS `EV`,
Month(szallito_fejlec.Datum) AS `HO`,
SUM(Szallito_tetel.Szallitasi_darab* Szallito_tetel.Ar*(1-
Szallito_tetel.Kedvezmeny/100)) AS `ERTEK`,
termek_torzs.Termek_tipus
FROM szallito_tetel
JOIN szallito_fejlec ON (szallito_tetel.Szallito_sorszam = szallito_fejlec.Szallito_sorszam)
JOIN termek_torzs ON (szallito_tetel.Termek_kod = termek_torzs.Termek_Kod)
GROUP BY szallito_tetel.Telephely_kod,
szallito_fejlec.Uzletkoto_kod,
Year(szallito_fejlec.Datum),
Month(szallito_fejlec.Datum),
termek_torzs.Termek_tipus;
takes almost 10 minutes to run ( szallito_fejlec table has approx. 2200000 rows, Szallito_tetel - 5530000 rows and termek_torzs 39000 rows) with MySqlClient SelectCommand.Timout turned to 2000 MySql.
Similar query on SQL server with the same data imported (SQLEXPRESS 2005) runs under 50 seconds, giving it some analyzed indexes it takes even less time.
Both servers are on Intel DualCore motherbord of 2.4 GHz with 2 Gbyte RAM running WindowsXP.
The goal is to make MySql response enjoyable.
The tables have all reasonable indexes (customer created, reflecting most used "where"-s in their application).
What are the guidelines/steps from moving from the "dead" point?
Any assistance would be greatly appreciated.