How to delete data efficiently without time consume in my sql-CodePudding

I have created query to delete data in the table(iam using sub query). table_a contains 21000 data. when i delete using the below query, it take much more time. How to delete data with the below query, using some modification.

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

CodePudding user response：

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

CodePudding user response：

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.