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

  • RT : 昨年開催されたOracle MySQL Innovation Day 2018 秋の模様がEnterpriseZineのDB Onlineに掲載されました / MySQL 8.0の新機能から寿司ビール問題まで――Oracle MySQL Innovation Day 2018 秋 via ,
  • If you have a ticket for the pre-FOSDEM Day and you are not able to join, please release your ticket, there are people on the waiting list ! Thank you in advance ! ,
  • When your laptop dies just before 2 weeks of conferences... ,
  • RT : EVENTS:  MySQL Day, Denver, CO Forget Skiing. On Tuesday, January 29, 2019 Denver is all about NoSQL + SQL = MySQL. Join us to learn about NoSQL+SQL, plus how to improve your database performance, secure your data and prevent downtime. Register today! ,
  • RT : MySQL is ubiquitious for cloud usage. In this series I will explore Oracle Cloud's IaaS offerings that support MySQL. In this particular post I will highlight IaaS Enterprise capabilities that help secure our cloud perimiter. ,
Locations of visitors to this page
categories