CODEWITHSUNDEEP
mysql
Noufal netspective
Noufal netspective

Reputation: 111

How to delete data efficiently without time consume in my sql

I want to delete data from table_a, using the query below, but it is very slow.
table_a contains 21000 data.

DELETE FROM table_a WHERE id IN (
    SELECT 
        GROUP_CONCAT(N.id) 
    FROM table_a N 
    INNER JOIN table_b E ON N.form_id=E.form_id AND N.element_id=E.element_id  
    WHERE N.option_value=0  
    AND E.element_type IN('checkbox','radio','select')
)

Is there a more efficient way?

Upvotes: 0

Views: 73

Answers (2)

Sam020
Sam020

Reputation: 381

Just to offer an alternative to juergen_d's answer, you can also create > insert > rename > drop.

CREATE TABLE temp_source_table LIKE source_table; -- indeces are copied

INSERT INTO temp_source_table SELECT * FROM source_table WHERE <conditions>; 
-- you SELECT which data you want to save instead of delete

-- maybe do some checks and balances before continuing with ...
RENAME TABLE source_table TO origin_source_table, temp_source_table TO source_table;
DROP TABLE origin_source_table;

Of course this depends on the usecase of the table in question, but on tables with large amounts of data and/or complex indeces (don't know the tipping point) this could be a faster option.

This would result in:

CREATE TABLE temp_table_a LIKE table_a; -- indeces are copied

INSERT INTO temp_table_a
SELECT N.*
FROM table_a N
    LEFT JOIN -- because you select what you want to save
    table_b E ON 
    N.form_id = E.form_id AND N.element_id = E.element_id
WHERE 
    NOT(N.option_value = 0 AND E.element_type IN('checkbox', 'radio', 'select')); 
-- you select which data you want to save instead of delete
-- check/tweak the result of the select before using it as part of the insert

-- maybe do some automatic checks on temp_table_a before continuing with:
RENAME TABLE table_a TO origin_table_a, temp_table_a TO table_a;
DROP TABLE origin_table_a;

Upvotes: 0

juergen d
juergen d

Reputation: 204756

You don't need a subselect. You can directly refer to the table you want to delete from in your statement like this:

DELETE N
FROM table_a N 
INNER JOIN table_b E ON N.form_id = E.form_id 
                     AND N.element_id = E.element_id 
                     AND E.element_type IN('checkbox','radio','select')
WHERE N.option_value = 0

Upvotes: 1

Related Questions