Is the review of SQL statements the only valid way to optimize them ?

Asking the question is already answering to it ;) I had a very long query that was even locking the table during the select on innodb (select into, that's due to the binlog). To avoid that query I decided to replace the select into statement by a select into outfile and add a second statement to perform the load data infile. The query was running for 18 hours !! It was impossible to change it, but the amout of data was huge, more than 100M rows (>1.2GB of returned data). I was able to reduce the running time by 9 : now the query runs in 1h50 ! How ? by adding SQL_BIG_RESULT to my select and by modifying two MySQL variables : set global join_buffer_size=67108864; set global query_cache_size=67108864;

Asking the question is already answering to it 😉

I had a very long query that was even locking the table during the select on innodb (select into, that’s due to the binlog).
To avoid that query I decided to replace the select into statement by a select into outfile and add a second statement to perform the load data infile.

The query was running for 18 hours !!

It was impossible to change it, but the amout of data was huge, more than 100M rows (>1.2GB of returned data).

I was able to reduce the running time by 9 : now the query runs in 1h50 !

How ?

by adding SQL_BIG_RESULT to my select and by modifying two MySQL variables :

set global join_buffer_size=67108864;
set global query_cache_size=67108864;

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One comment

  1. This has been a great help! Now i wont have trouble with a very long query that would lock a table during the select. Cheers! – J. Kale Flagg

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.