Make two different updates with two different where conditions Although this question is very similar, I don't think that it is also the best approach for my case, though it seemed to work. Consider the following example:
CREATE TABLE messages (
  writer VARCHAR(5),
  recipient VARCHAR(5),
  show_for_writer TINYINT(1), -- 0 for hidden, 1 for display
  show_for_recipient TINYINT(1) -- idem
);

I now want to create ONE update query which hides all messages of user x for him; i.e. which sets show_for_writer equal to 0 where the writer is x, AND which sets show_for_recipient equal to 0 where the recipient is x. Note that I want to make this as efficiently as possible, as this table may contain millions of records (one per message) for countless writer-recipient combinations (composite, non-unique index).
What I've tried to make, according to the linked post, is:
UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

I however feel that this is not ideal AT ALL, because it goes through all the records of the table, and this query may not be done at a regular scale, but not rarely either. What's the best solution for this? I'm open for any solution, even structural column modification propositions; I'm using MariaDB 10.3.
An Idea I had was maybe to edit the structure as follows:
CREATE TABLE messages (
 participants VARCHAR(10) FULLTEXT,
 writer VARCHAR(5) NOT NULL,
 recipient VARCHAR(5) NOT NULL,
 show JSON NOT NULL
);

And do something like:
UPDATE messages
SET show = JSON_REPLACE(messages,'$.x',0)
WHERE MATCH(participants) AGAINST('x');

The problem here is that I query messages if they shall be shown for 'x' upon init. Hence, setting show to a JSON data type won't make it possible to index this column, hence to query in f(show) efficiently. That's why I thought this idea may not be ideal, but just to show you that I'm basically open even to modifications at the structural level.
Otherwise I could simply fire two subsequent UPDATE statements, like so:
UPDATE messages
SET show_for_writer = 0
WHERE writer = 'x';

UPDATE messages
SET show_for_recipient = 0
WHERE recipient = 'x';

But I really prefer to stick to using one query per action. Help please!
Check the example here, with the update query to be otpimized to avoid going over all records of the table:
http://www.sqlfiddle.com/#!9/f6830d/1
Do two UPDATEs.
And have a PRIMARY KEY
And two secondary indexes:
INDEX(recipient),
INDEX(writer)

If you are concerned about ACIT integrity, make a transaction:
BEGIN;
UPDATE ... WHERE writer...
UPDATE ... WHERE recipient...
COMMIT;