2

I have a table imp_acc_act_itm that contains around 36 Millions records, and the following query runs very fast and returns only 21 records from this table in less than 5ms

SELECT SKU
  FROM imp_acc_act_itm AS ii
 WHERE exists
 (
   SELECT 1 
     FROM acc_opr_rule_skus AS rs
    WHERE rs.object IN (2006)
      AND ii.sku = rs.value 
 );

Now when trying to insert those record into another table, or creating a temporary table from it, it takes more than a minute to finish:

CREATE TEMPORARY TABLE temp_table AS
SELECT SKU
  FROM imp_acc_act_itm AS ii
 WHERE exists
 (
   SELECT 1 
     FROM acc_opr_rule_skus AS rs
    WHERE rs.object IN (2006)
      AND ii.sku = rs.value 
 );

Anyone has an explanation for that?

Please note that I am using MariaDB-10.3.14 with InnoDB Engine. And here is the Explain of the Select Query:

+------+-------------+-------+------+--------------------------------------------------------------------+--------------------------+---------+----------------------+------+-------------+
| id   | select_type | table | type | possible_keys                                                      | key                      | key_len | ref                  | rows | Extra       |
+------+-------------+-------+------+--------------------------------------------------------------------+--------------------------+---------+----------------------+------+-------------+
|    1 | PRIMARY     | rs    | ref  | PRIMARY,FK__acc_opr_rule_skus__object,FK__acc_opr_rule_skus__value | PRIMARY                  | 4       | const                |    1 | Using index |
|    1 | PRIMARY     | ii    | ref  | FK__imp_acc_act_itm__sku                                           | FK__imp_acc_act_itm__sku | 4       | eshop_fadel.rs.value |  238 | Using index |
+------+-------------+-------+------+--------------------------------------------------------------------+--------------------------+---------+----------------------+------+-------------+
ImaDaou
  • 23
  • 3
  • You didn't tell us which version of MySQL you're running, or which database engine. I read that changing the database engine might help: `ENGINE=MyISAM`. They also often refer to memory problems. Having the output of an `EXPLAIN` might also help to discover what's going on. – KIKO Software Feb 18 '22 at 11:00
  • If your final table is so compact then (1) specify table structure explicitly (2) add `ENGINE = Memory` to your table definition. `CREATE TEMPORARY TABLE temp_table (SKU INT) ENGINE = Memory AS SELECT SKU ...` – Akina Feb 18 '22 at 11:51
  • @KIKOSoftware, thank you for your comment, I am using MariaDB-10.3.14 with InnoDB Engine. I updated the question and added also the explain of the select query. I will try with MyISAM engine and get back to you. – ImaDaou Feb 18 '22 at 11:53
  • The `EXPLAIN` for the select query looks good. Could you also add the `EXPLAIN` for the table creation query? It should be similar, but you never know. – KIKO Software Feb 18 '22 at 11:58
  • This may also be useful: Those 5 ms are a bit quick. This is probably a cached query. So to test the real speed of a query you need to switch off caching with: [SQL_NO_CACHE](https://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html). One explanation could be that the naked select query uses the cache and the one in the create table query not. – KIKO Software Feb 18 '22 at 12:05
  • @Akina, thanks for your comment, I tried to create the temp table ENGINE=Memory with same result. I also tried to insert into a normal InnoDB table. I will retry to create a similar scenario on a fresh installation of latest version of MariaDB, to see if this will be solved. – ImaDaou Feb 18 '22 at 12:07
  • @KIKOSoftware, I added SQL_NO_CACHE and got same result in less than 5 ms. About the "EXPLAIN for the table creation", how can I do that? as far as I know, I can only Explain a Select Query not an Insert or Create – ImaDaou Feb 18 '22 at 12:15
  • Sorry, yes, explain doesn't work table creations. That caching thing was worth a try... – KIKO Software Feb 18 '22 at 12:52

1 Answers1

1

I do not see what makes it slow, but this might be a workaround:

CREATE TEMPORARY TABLE temp_table (SKU ...);

INSERT INTO temp_table
    SELECT SKU
      FROM imp_acc_act_itm AS ii
     WHERE exists
     (
       SELECT 1 
         FROM acc_opr_rule_skus AS rs
        WHERE rs.object IN (2006)
          AND ii.sku = rs.value 
     );

Another thought is to turn the SELECT around:

SELECT rs.value AS SKU
    FROM ( SELECT DISTINCT value
               FROM acc_opr_rule_skus 
               WHERE rs.object IN (2006) ) AS rs
    JOIN imp_acc_act_itm AS ii ON ii.sku = rs.value

At that point these indexes may help a lot:

acc_opr_rule_skus: INDEX(object, value)
imp_acc_act_itm:  INDEX(SKU)   -- unless it is the `PRIMARY KEY`
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hello and thank you for your reply. The first workaround of the temporary table has same slow result. The second workaround of using a JOIN instead of the EXISTS has much better result on the Insert which can fix the issue (but the old select using EXISTS had double speed of the JOIN). I also found [this thread](https://stackoverflow.com/questions/19269122/why-is-mysql-insert-into-select-so-much-slower-than-a-select-alone) with a workaround of SELECT INTO OUTFILE, then INSERT FROM INFILE which has a good performance. I am just wondering why this is happening. – ImaDaou Feb 18 '22 at 23:28
  • @ImaDaou - Thanks for the feedback. I would be very surprised if the 2-step, via a file, OUTFILE+INFILE would be even in the running! – Rick James Feb 18 '22 at 23:42
  • 1
    This whole issue is very weird. I don't know how, but yes the outfile is faster. if I had some time, I will try to prepare a "how to reproduce code". – ImaDaou Feb 19 '22 at 08:42