How to delete rows from the mid of the table without any where condition?

Question

I have a table testing with two columns

• id: auto_increment
• name: varchar(20)

I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records from MySQL table (InnoDB engine). How can I achieve this?

This table I have created is just for testing purposes. My actual table contains billions of records. One way I thought as:

delete from testing where id in (select group_concat(id) from testing limit 1, 2);

I think this would be quite slow in case of my actual table.

Can anyone provide any better solution?

Answer 1

What you are asking for can lead to unpredictable results in certain situations.

What kind of situations?

1. Running this on a Master and the Slave receives the same SQL. The problem I see is that rows of data in a Master may not be physically written in the same order on the Slave.
2. Triggers will encounter issue (or really create issues) with this type of DELETE query

I am sure there are other situations, but these came to mind first.

I addressed these before

• Feb 22, 2011 : Problem with MySQL subquery
• Jul 30, 2012 : Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

SUGGESTION

If you look back at the original question (Problem with MySQL subquery), the OP suggested getting the key for the row you want deleted. Then issue the delete using that key. It's the safest way.

YOUR ACTUAL QUESTION

Doing fancy DELETEs, even if possible, could produce unwantd results on Slave, on a Master, or even a Standalone DB Server when not using WHERE.

In the end, you must use WHERE for the safest and most expected results.