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

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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;

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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 *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories