Duration of query and fetching too long...

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

Hi Medmed,

Could you tell us the OS you use please.

Regards,

Lukaku92.

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

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

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 :)

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

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

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.