You may not be aware of this, but did you know that MySQL allocates a distinct join buffer for each additional JOIN clause ?
According to the MySQL Documentation for join_buffer_size:
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.
The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).
For additional information about join buffering, see Section 184.108.40.206, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 220.127.116.11, “Block Nested-Loop and Batched Key Access Joins”.
This is also declared in Block Nested-Loop Join Algorithm
MySQL join buffering has these characteristics:
Join buffering can be used when the join is of type ALL or index (in
other words, when no possible keys can be used, and a full scan is
done, of either the data or index rows, respectively), or range. Use
of buffering is also applicable to outer joins, as described in
Section 18.104.22.168, “Block Nested-Loop and Batched Key Access Joins”.
A join buffer is never allocated for the first nonconstant table, even
if it would be of type ALL or index.
Only columns of interest to a join are stored in its join buffer, not
The join_buffer_size system variable determines the size of each join
buffer used to process a query.
One buffer is allocated for each join that can be buffered, so a given
query might be processed using multiple join buffers.
A join buffer is allocated prior to executing the join and freed after
the query is done.
Based on this, you must increase the join_buffer_size in your session just before running your
INSERT ... SELECT. From the look of the join's
ON clauses, I don't foresee indexes ever being selected by the query optimizer.
I cannot tell you how big to make the join_buffer_size. You will have to experiment with it and run the
EXPLAIN plan to see if the
SELECT will avoid writing to disk.
WARNING : According to the last paragraph of MySQL Documentation on
Nested-Loop Join Algorithms:
The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger.
This why you should experiment with join_buffer_size.
You can change the join_buffer_size one MB at a time. Then, run the
SELECT and time it.
STEP 01 : Set an initial size of 0 and initial runtime of 48 hours (172800 seconds)
SET @jbsize = 0;
SET @prev_runtime = 172800;
STEP 02 : Increment 1MB
SET @jbsize = jbsize + 1;
SET SESSION join_buffer_size = @jbsize * 1048576;
STEP 03 : Time the
SET @t1 = UNIX_TIMESTAMP();
SELECT p.id_people, 146
FROM people p
INNER JOIN sell s
ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46')
INNER JOIN group g
ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c
ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN company co
ON (co.id_company = c.id_company)
WHERE g.type = 'OD'
AND g.start < '2017-08-09 13:29:12.46'
AND g.end > '2018-08-09 13:29:12.46'
SET @t2 = UNIX_TIMESTAMP();
STEP 04 : Compute Time
SET @curr_runtime = @t2 - @t1;
SET @diff_runtime= @prev_runtime - @curr_runtime;
STEP 05 : Decision Time
Improvement_From_Last_Run is big in terms of hours, minutes, and seconds and you feel making join_buffer_size bigger would improve the run time, loop back to
STEP 02. Keep doing this until
GIVE IT A TRY !!!