Mysql tables and deletion strategies

Question

I work on a social network like a subset of Facebook. I think this means the application will be more difficult to read than a heavy record (i.e. More SELECT than INSERTS, UPDATES or DELETES)

I plan to use MySQL for the database using MyISAM. Each table in the database will contain the following three fields:

• CREATED - date field containing the time the record was created
• UPDATED - date field containing the time when the record was changed.
• ROWSTATUS - CHAR field (1) containing one character of the character to indicate whether the record is active, inactive or deleted (using the values ​​"A", I and D respectively).

Through the PHP wrapper class, we ensure that all SELECT queries include ROWSTATUS queries, and UPDATE also updates the updated column, and INSERT queries update the CREATED column.

I plan on not actually deleting the entries, rather than updating by writing the ROWSTATUS field to D to show that it has been deleted (i.e. soft deletion).

We have an SQL procedure that physically deletes deleted data after 10 days.

However, I survived this article , which states that there is no need to physically delete it due to blocking overhead. Rather, the author suggested using this scheme:

 SELECT e.eventid,e.title FROM events e WHERE NOT EXISTS (SELECT * FROM event_deletes ed WHERE ed.eventid = e.eventid); 

I am wondering how my circuit compares with this proposed mechanism, and which is better? I could not reach for any final answer on my own.

Answer 1

As @ Pentium10 says, there is nothing wrong with your plan. This is actually a fairly standard approach.

The problem is that if you use MyISAM, your UPDATEs will lock the entire table during query execution. This creates a bottleneck because you can only update or delete one record at a time.

If you have no reason to use MyISAM, I would recommend switching to InnoDB as a database engine. InnoDB uses row level locking, so your UPDATE queries will not block other UPDATEs. It also has some other interesting features, such as transaction support and referential integrity restrictions.

Answer 2

The only problem I see here, compared to this article, is that you only handle locks for calling DELETE.

You should know that the UPDATE and DELETE statements must always throw an exclusive lock on the MyISAM table.

This is why the article recommends using INSERT instead of UPDATE rowstatus. You have to go, as the article says. Create a dedicated table to store deleted identifiers and use the recommended connection to select to retrieve non-deleted records. That way, when you delete the end user, you simply insert into the table and you will not block UPDATE in the table. If you add the correct keys to both tables, the union will be performed only by indexes, si will be fast on SELECT.

You also enter overhead if you save update time. You should abandon this idea, as it is useless, and you will not use to tell when the record was updated.