First, a query or (update) statement with a condition like
WHERE posts < comments that compares 2 columns cannot effectively use your indexes so it will probably have to do a full table scan. It might be better if you had a composite index,
(posts, comments) or the other way around, but it would still need to do a full index scan.
If the rows to be updated are very few, say 100, it's not very efficient to scan 1 million rows to update only 100.
So, the easiest thing would probably be to add such a composite index and test. If the resulting efficiency from the full index scan is acceptable, you can keep using this query.
Another thing you could do - since you are using MariaDB - is to add a
VIRTUAL (computed) column and index it:
ALTER TABLE posts
ADD COLUMN comment_posts_diff INT AS (columns - posts) PERSISTENT,
ADD INDEX more_comments_ix (comment_posts_diff) ;
Then you only need to change the condition of your queries to use:
WHERE comments - posts > 0
WHERE comment_posts_diff > 0
and the index will be effectively used.
Note that there are 2 variants of computed columns, the
VIRTUAL and the
PERSISTENT ones. As it's obvious from the names, the persistent columns
actually take space on disks and the values are automatically modified when any of the columns in their definition is modified. Their advantage is that they can be indexed.
'more comments' though is just a constant string that applies to rows with
comments > posts and there is another string (
'less comment'?) that applies to the rest of the rows or a similar need, you don't really need the column
notes and any of the above. Your queries could just compute the respective value (more comments, less comment, no comments) at run time.
Or you could have a computed column to do the work for you (vitual this time, not persistent):
ALTER TABLE posts
DROP COLUMN notes,
ADD COLUMN notes VARCHAR(20) AS
CASE WHEN columns > posts THEN 'more comments'
WHEN columns = 0 THEN 'no comments'
WHEN columns = posts THEN 'comments = posts'
ELSE 'less comments'