Does Deleting Row from View Delete Row from Base Table - MySQL

Does deleting row from view delete row from base table - MySQL?

Yes, it will. The only thing to watch out for, is permissions.

Quoting official docs

Some views are updatable. That is, you
can use them in statements such as
UPDATE, DELETE, or INSERT to update
the contents of the underlying table.
For a view to be updatable, there must
be a one-to-one relationship between
the rows in the view and the rows in
the underlying table. There are also
certain other constructs that make a
view nonupdatable.

Does deleting rows from table effect on db performance?

What percentage of the table is "deleted"?

If it is less than, say, 20%, it would be hard to measure any difference between a soft "deleted=1" and a hard "DELETE FROM tbl". The disk space would probably be the same. A 16KB block would either have soft-deleted rows to ignore, or the block would be not "full".

Let's say 80% of the rows have been deleted. Now there are some noticeable differences.

In the "soft-delete" case, a SELECT will be looking at 5 rows to find only 1. While this sounds terrible, it does not translate into 5 times the effort. There is overhead for fetching a block; if it contains 4 soft-deleted rows and 1 useful row, that overhead is shared. Once a useful row is found, there is overhead to deliver that row to the client, but that applies only to the 1 row.

In the "hard-delete" case, blocks are sometimes coalesced. That is, when two "adjacent" blocks become less than half full, they may be combined into a single block. (Or so the documentation says.) This helps to cut down on the number of blocks that need to be touched. But it does not shrink the disk space -- hard-deleted rows leave space that can be reused; deleted blocks can be reused. Blocks are not returned to the OS.

A "point-query" is a SELECT where you specify exactly the row you want (eg, WHERE id = 123). That will be very fast with either type of delete. The only possible change is if the BTree is a different depth. But even if 80% of the rows are deleted, the BTree is unlikely to change in depth. You need to get to about 99% deleted before the depth changes. (A million rows has a depth of about 3; 100M -> 4.)

"Range queries (eg, WHERE blah BETWEEN ... AND ...) will notice some degradation if most are soft-deleted -- but, as already mentioned, there is a slight degradation in either deletion method.

So, is this my "opinion"? Yes. But it is based on an understanding of how InnoDB tables work. And it is based on "experience" in the sense that I have detected nothing to significantly shake this explanation in about 19 years of using InnoDB.

Further... With hard-delete, you have the option of freeing up the free space with OPTIMIZE TABLE. But I have repeatedly said "don't bother" and elaborated on why.

On the other hand, if you need to delete a big chunk of a table (either one-time or repeatedly), see my blog on efficient techniques: http://mysql.rjweb.org/doc.php/deletebig

(Re: the PS)

SELECT without an ORDER BY -- It is 'fair game' for the query to return the rows in any order it feels like. If you want a certain order, add ORDER BY.

What Engine is being used? MyISAM and InnoDB work differently; neither are predictable with out ORDER BY.

If you wanted the new entry to have id=6, that is a different problem. (And I will probably argue against designing the ids like that.)

MySQL: Remove row from join view with/without touching rows in underlaying tables

1) Can I delete row from my_view without deleting related rows from table1 and table2?

No, a view doesn't remember anything by itself, it can be seen as a saved query against table1 and table2. If you want to delete a value from the view, it must also be deleted (or otherwise marked as not showing up in the view query) in the underlying tables.

2) Can I delete row from my_view with deleting related row from table1 but not deleting related row from table12?

You cannot do that through the view (DELETE against multi-table updatable views is not supported in MySQL), but you can of course delete the data directly in table1 and the view will reflect the changes immediately.

MySQL new row appears after deleting row record

to close this question, adding the same as answer

Make copy of this table into another table.. and try deleting the rows.

Does the space occupied by deleted rows get re-used?

It is paranoia :)

DB's don't grow in size unnecessarily, but for performance issues space is not freed either.

What you've heard most probably is that if you delete records that space is not given back to the Operating System. Instead, it's kept as an empty space for the DB to re-use afterwards.

This is because:

• DB needs to have some HD space to save its data; if it doesn't have any space, it reserves some empty space at first.
• When you insert a new row, a piece of that space is used.
• When you run out of free space, a new block is reserved, and so on.
• Now, when you delete some rows, in order to prevent reserving more and more blocks, its space is kept free but never given back to the Operating System, so you can use it again later without any need of reserving new blocks.

As you can see, space is re-used, but never given back. That's the key point to your question.

---

Related Topics