MYSQL Select Query Very Fast, but creating temporary table from this select is too slow-CodePudding

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 |
 ------ ------------- ------- ------ -------------------------------------------------------------------- -------------------------- --------- ---------------------- ------ -------------

CodePudding user response：

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`