deleting rows from table

Rui Barbosa posted 11 months ago in General

why sometimes ctrl+del doesnt work to delete rows ? and why does it take so much time to delete multiple rows (200 or soemthing like that)

ansgar posted 11 months ago

Deleting rows in the data grid is a bit slow because there are some special cases to keep in mind for HeidiSQL. For example, a row is by default loaded with the first 256 characters in text columns only, for performance reasons. Now when you delete a row, HeidiSQL has to make sure it has the full data, because on a table without primary or unique key, it has to take full data into the WHERE clause of the DELETE command.

In short, deleting in the data grid is quite safe, but slow. Deleting via custom query can be equally secure but faster. However, that needs deeper knowledge about the table in question and its keys, and that is more writing work.

renith posted 10 months ago

Use the DELETE statement without specifying a WHERE clause. With segmented table spaces, deleting all rows of a table is very fast. ... Use the TRUNCATE statement. The TRUNCATE statement can provide the following advantages over a DELETE statement: ... Use the DROP TABLE statement.

Misha v.3 posted 10 months ago

I'd say you posted a bit strange "recommendations"...

Use the DELETE statement without specifying a WHERE clause

Delete all rows != delete selected rows. I have no idea why someone want to delete everything instead of a selected rows.

With segmented table spaces, deleting all rows of a table is very fast

It is not fast with transactional table (innodb). The more rows deleted -- the slower the delete usually.

Use the TRUNCATE statement

This does not work if you have foreign keys.

Use the DROP TABLE statement.

Super. In production. And then try to re-create everything (with indices, constrains, etc).

Please login to leave a reply, or register at first.