php - Need to insert in products table from more than one tables with dynamic user id

Question

I have 3 tables users, products and temp_table. i have imported an xlsx file having 1,00,000 records in to temp_table. now i have to insert these records to products table. and here i have to save user_id from users table to products table as well. Note: user_id is dynamic(i.e. in xlsx file there is a column called email and I have created a new user for their email). so in products table user_id will be dynamically inserted. I have used below query, but it is taking too much time. and sometimes my MySQL get locked.

INSERT INTO products
        (user_id,
        brand_id,
        points_discount,
        amount,
        sub_total,
        added_on)
        SELECT users.user_id, 
            brand_id,
            discount,
            amount,
            sub_total,
            added_on
        FROM temp_table
        INNER JOIN users 
        ON email = users.email;

Please help me to solve this problem.

标签： phpmysqllaravellaravel-5

Answer 1

在和表的email列上创建索引。还要确保两列具有相同的temp_tableusersdatatype

ALTER TABLE temp_table ADD KEY `idx_email` (`email`);

ALTER TABLE users ADD KEY `idx_email` (`email`);