10.6.7 INSERT INTO (SELECT FROM....) types of queries exponentially slower since upgrading from 10.1 to 10.6 - Source

Revision: 119339
User: James Cross
Date: 2022-09-22 08:34

For the most part upgrading to 10.6 was relatively pain free but there are a few queries that are performing very badly and they share one thing in common.  They are basically pulling data from a very large table and inserting the result into another.

An example below...  As is, this takes 30 minutes to run.  If you pull the results to a script and then insert its under a second and was under a second before the upgrade.  I have looked at the explain and that doesn't help as its showing the same for the select and nothing helpful for the insert.

We have a discounts table and looking at a weeks worth of data we want to pull anything that matches from a huge journal and update any previous ones it pulled.  I can get in to the nitty gritty detail of sizes, and indexes later on but for now, can anyone see why pulling and inserting should be exponentially slower than pulling it out and then inserting one by one like a noob would?  It should never be quicker to pull he data out in PHP and then insert row by row - I'd be better off using a text file if that was the case.

Its a pain because we rely on the database doing the heavy lifting - its not just this query its similar ones like its where there is an insert into (select from...) and even if the table you're inserting into just contains a few rows.

<<code>>
INSERT INTO discounts
                            SELECT
                            @yw yearweek,
                            DATE(j.time) date,
                            j.branch_code,
                            j.till,
                            j.user,
                            j.transaction,
                            ROUND(value / 100, 2) value,
                            CASE
                                WHEN
                                    SUBSTR(SPLIT_STRING(j.information, '~', 2),
                                        1,
                                        3) = 'XPO'
                                THEN
                                    SUBSTR(SPLIT_STRING(j.information, '~', 2),
                                        5,
                                        99)
                                ELSE SPLIT_STRING(j.information, '~', 2)
                            END payroll_id,
                            SPLIT_STRING(j.information, '~', 1) card_number,
                            NULL,
                            NULL
                        FROM
                            fftillrep.journal j
                        WHERE
                            j.type = 15 AND j.subtype = 54
                                AND j.time BETWEEN @start AND @end
                          -- remove overrings
                          AND j.utrans NOT IN (SELECT utrans FROM transaction_summary s WHERE s.overring != 0 AND s.time BETWEEN @start AND @end )
                    
                    ON DUPLICATE KEY UPDATE 
                        user = VALUES(user),
                        value = VALUES(value),
					    payroll_id = VALUES(payroll_id),
                        card_number = VALUES(card_number);
<</code>>

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

10.6.7 INSERT INTO (SELECT FROM....) types of queries exponentially slower since upgrading from 10.1 to 10.6 - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB