MySQL query taking forever to run, what is wrong?

Question

I am using this to make a query where i goes upto 20 in c#.net mysql connectivity. This query takes forever to execute and when I run it in MySQL shell it takes 3 mins. All other queries run fine.

string sqlQuery = "(SELECT TimeStamp, AVG(FwdHr), W FROM Meter_Data " + 
                    "WHERE TimeStamp Between " + 
                    (timeSt[0] - window).ToString() + 
                    " AND " + (timeSt[0] + window).ToString() + 
                    " AND DeviceID = '"+deviceId+"')";

for (int i = 1; i < timeSt.Count; i++)
{
    sqlQuery = sqlQuery+ " UNION (SELECT TimeStamp, AVG(FwdHr), W 
               FROM Meter_Data WHERE TimeStamp Between " + 
               (timeSt[i] - window).ToString() + " AND " +
               (timeSt[i] + window).ToString() + 
               " AND DeviceID = '" + deviceId + "')";  
}

What is the approximate size of `Meter_Data` table? And how many items is there in `timeSt` list? — Andrei, Jun 13 '13 at 11:58
`... in mysql shell it takes 3 mins ...` -uh how many unions exist when you run the query in the MySQL shell? Are you **absolutely sure** you're running the same query? — Mike Perrenoud, Jun 13 '13 at 11:59
In terms of performance you might have better luck without a union query. Replace it with "and deviceid in (list of numbers)" — Dan Bracuk, Jun 13 '13 at 12:12

score 0 · Answer 1 · answered Jun 13 '13 at 12:11

0

Things to check:

Log the queries being executed from your C# code. You may "think" the same query is being executed from MySQL shell, but there's always a chance it may not be.
Look at the query plan for the query and see what indexes, etc. are being used. You may need to create an index on Timestamp, which may help performance tremendously.

answered Jun 13 '13 at 12:11

dcp

54,410
22
144
164

i am using one more union query which is running fine and in this query when i remove union it also runs fine . if there is something wrong with union then other query should not run also and if there is some connectivity problem then why its running without union?? one thing is when i debug this - with union it stops at datareader execution time for some time and then throws exception timout - but without union it waits for that time but returns with value.. – Inderpal Singh Jun 13 '13 at 12:26
@Inderpal Singh - Did you follow my suggestion of looking at the query plans in those scenarios? Recommended reading: http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html – dcp Jun 13 '13 at 12:39
i am new in my sql - i don't know this much – Inderpal Singh Jun 13 '13 at 12:53
one thing i found is when i also give query a meter id - i runs fine .. and i am using timestamp+deviceid+meterid combo as primary key and also i have maintained it as index – Inderpal Singh Jun 13 '13 at 13:02
can you please see this - http://stackoverflow.com/questions/17088020/my-sql-timeouts-if-where-doesnot-include-all-columns-of-primary-key-combo-whats – Inderpal Singh Jun 13 '13 at 13:17
@Inderpal Singh - "i am new in my sql - i don't know this much " It's a good time to learn then, that's why I posted the link. Determining why your queries run slowly is one of the most important skills you can develop as a SQL programmer, I highly recommend learning how to use query plans. As for your index, you may want to try creating an index just on timestamp. But again, looking at the query plan will tell you if the index is being used or not, that's why it would be good for you to do it. – dcp Jun 13 '13 at 14:02
thanks buddy.. actually i am working on some project - tomorrow is deadline .. thats why i was little restless.. i will definitely see execution plan and indexes.. – Inderpal Singh Jun 13 '13 at 14:41

MySQL query taking forever to run, what is wrong?

1 Answers1