Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Duration of query and fetching too long...

medmed's profile image medmed posted 4 weeks ago in General Permalink

Hi everyone, I love HeidiSQL since a long time. But my love story began to change since I have started to face issue with the executing time of my queries...

My query has this face : SELECT * FROM table_1 T1 JOIN table_2 T2 ON T2.A=T1.A

Very simple query to explain my issue. table_1 has approximately 79k rows and table_2 has 128k. More than 20min to get the final result. That's ridiculous.

I have found that is the fetching time that is exploding.

I also tried on mysql but same story. The weirdest point is that for a similar query made on oldest tables and much bigger, the executing time is lower. All the data are stored in a local server.

I would be glad if some of you can help me. I have already searched on the web but I didn't find any solution.

Thank you for your help. :)

Respectfully,

Medmed

Lukaku92's profile image Lukaku92 posted 4 weeks ago Permalink

Hi Medmed,

Could you tell us the OS you use please.

Regards,

Lukaku92.

medmed's profile image medmed posted 4 weeks ago Permalink

Hi, I am using Windows 10. My Heidi SQL version is 10.2.0.5599. I forgot to say that I work with MariaDB.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 4 weeks ago Permalink

For such large tables you should create a key on the "A" colums in both tables.

medmed's profile image medmed posted 4 weeks ago Permalink

Thank you very much for your help. It was much more simple than I expected. I have an other issue that annoided me. We are several to be connected to this same database through an app. Some queries we make lead to a 'Lock wait timeout exceeded' and it happens incrementally. What do you think can be the reason ? Is there a maximum calculus capacity the database can handle ? If yes what solutions remain or how can we adapt our use of the db ?

Thank you very much :)

ansgar's profile image ansgar posted 4 weeks ago Permalink

Would you mind telling us your simple solution to your initial question?

medmed's profile image medmed posted 4 weeks ago Permalink

It was due to the fact I did not have a key on the specified columns as you suggested. I added one on both of them and now it is working properly and quickly. Thank you again for your help

ansgar's profile image ansgar posted 4 weeks ago Permalink

Great!

About these "Lock wait timeout exceeded": you probably have some long-running query which locks one or more tables, and other processes are in the pipeline which then get that timeout. You can of course raise the lock_wait_timeout variable to some higher value, but I recommend debugging your query which takes too long.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.