0

I am trying to run a raw SQL statement to grab some data for reporting. The query itself is big and takes about 2seconds to execute. When I remove the 'GROUP BY' in my query in order to make it faster (of course in this case I'll handle the grouping in Python code), the query runs inside SQLYog in about 0.036 seconds. However, without the grouping it returns around 300K rows (compared to ~200 rows with grouping).

Anyways, when running the query inside my code, it takes about 60 seconds to finish without the grouping. While it takes about 2 seconds to finish with the grouping. I am not certain, but I believe this difference in time is due to the number of rows returned.

So my question is: is my understanding correct? is it because we have a large dataset without 'GROUP BY' that it takes about 60 seconds? and, is there anything I can do in Python to make this faster?? The difference between SQLYog and my Python app is huge... 0.036 seconds to 60 seconds??!! strange.

I run my query using raw query execution like this: db.session().execute(sql, {"param1": value1, "param2": value2, "param3": value3})

My query has a lot of joins and the tables do have indices on the columns being joined on.

Thanks

Mutasem Barjawi
  • 30
  • 9
  • Please provide the SQL for the query and `SHOW CREATE TABLE`. – Rick James Jul 06 '21 at 18:59
  • @RickJames thank you for your reply. Can't we answer the question as a generic question without having to get the query?? The query is complicated and long and not sure if I can expose the table structure here. Running the query using an SQL application like SQLYog works fine and runs fast.. this shows that it is not an issue of keys or indexes. Loading the rows in a Python project is slow. – Mutasem Barjawi Jul 14 '21 at 23:31
  • Are there JOINs? ORDER BY? LIMIT -- either explicit or implicitly added by SQLYog? Single transaction? Did you check each timing twice? Turn on the general log to see what differences there are between SQLYog and Python. – Rick James Jul 15 '21 at 01:54

0 Answers0