CODEWITHSUNDEEP
sql-servermysql
Verve Innovation
Verve Innovation

Reputation: 2096

How to optimize this MySQL query .It takes nearly 2 secs to execute

I recented converted my project from SQL SErver to MySQL .

I do see one my query which was executing faster in SQL server takes more time( 2 sec ) in mysql.

Am I doing anything wrong here .Help me to optimize it . Thank s

SELECT ReadingDateTime, SUM(DATA) AS PowerTotal FROM
LTMeterReading WHERE MeterID IN(59) and DataTypeID=48 and
ReadingDateTime >= '2010-04-01 12:00:00 AM' AND
ReadingDateTime < '2010-04-02 12:00:00 AM'
GROUP BY ReadingDateTime ORDER BY ReadingDateTime

Upvotes: 0

Views: 139

Answers (4)

Verve Innovation
Verve Innovation

Reputation: 2096

I finally fixed it by Changing the MySQL table engine to MyISAM ,after which its very fast . Thanks guys .

Upvotes: 0

Andrew dh
Andrew dh

Reputation: 879

how many rows in the table? what's the data type of DATA? any indexes on ReadingDateTime, MeterID, DataTypeID?

Upvotes: 0

zerkms
zerkms

Reputation: 254926

Try to add index, that covers DataTypeID + MeterID + ReadingDateTime in this particular order

Also - ORDER BY clause is obsolete here: after you have groupped by ReadingDateTime - data is already sorted ascendant by this field.

Upvotes: 1

Dani
Dani

Reputation: 15069

you can put indexes on the columns you check for, it might effect your insert time though.

Upvotes: 1

Related Questions