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

on

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;

One thought on “Is the review of SQL statements the only valid way to optimize them ?

Leave a Reply

Your email address will not be published. Required fields are marked *

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

recent

Last Tweets

Locations of visitors to this page
categories