![]() SELECT without an ORDER BY - It is 'fair game' for the query to return the rows in any order it feels like. 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: But I have repeatedly said "don't bother" and elaborated on why. With hard-delete, you have the option of freeing up the free space with OPTIMIZE TABLE. 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.įurther. ![]() But it is based on an understanding of how InnoDB tables work. ) will notice some degradation if most are soft-deleted - but, as already mentioned, there is a slight degradation in either deletion method. (A million rows has a depth of about 3 100M -> 4.) You need to get to about 99% deleted before the depth changes. ![]() But even if 80% of the rows are deleted, the BTree is unlikely to change in depth. The only possible change is if the BTree is a different depth. That will be very fast with either type of delete. Blocks are not returned to the OS.Ī "point-query" is a SELECT where you specify exactly the row you want (eg, WHERE id = 123). But it does not shrink the disk space - hard-deleted rows leave space that can be reused deleted blocks can be reused. (Or so the documentation says.) This helps to cut down on the number of blocks that need to be touched. That is, when two "adjacent" blocks become less than half full, they may be combined into a single block. In the "hard-delete" case, blocks are sometimes coalesced. Once a useful row is found, there is overhead to deliver that row to the client, but that applies only to the 1 row. There is overhead for fetching a block if it contains 4 soft-deleted rows and 1 useful row, that overhead is shared. While this sounds terrible, it does not translate into 5 times the effort. In the "soft-delete" case, a SELECT will be looking at 5 rows to find only 1. Now there are some noticeable differences. Let's say 80% of the rows have been deleted. A 16KB block would either have soft-deleted rows to ignore, or the block would be not "full". The disk space would probably be the same. 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". What percentage of the table is "deleted"? It should be considered as the good point of database that fill up the deleted spaces or something bad that leads to reducing the performance or none of them and doesn't matter what it's showing in front!? if deleting action happens lots of times, then primary column and its rows will be completely out of order and messed up. I've searched about this but most of the results were based on personal opinions or preferred ones and not referenced or tested data.ĪND Refering to the question and considering below pic, there's one more point to ask in this topic and I would be grateful if you could guide me.ĭeleting a row (row 6) while auto increment index was 225, leaded the not-sorted table to put the next inserted row by id=225 at deleted-id=6 place (at least visually!). Or keep the rows using 'delete' col and delete them for example monthly then re-index the data? Is it better to delete the rows instantly? Which way do you suggest to have a better database and performance?ĭoes deleting (maybe lots of) rows every day affect select queries for large tables? I'm working on a script using MySQL database with an auto-increment primary key tables, that users may need to remove (lots of) data rows as mistaken, duplicated, canceled data and so on.įor now, I use a tinyint last col as 'delete' for each table and update the rows to delete=1 instead of deleting the row.Ĭonsidering the deleted data as not important data,
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |