MySQL Forums
Forum List  »  Newbie

Slow retreieve under MyIsam
Posted by: Igor Bagrij
Date: November 16, 2009 11:25AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Slow retreieve under MyIsam
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
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.