I have this for simplicity fictional table which explains my problem:

  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `posts` int(11) NOT NULL,
  `comments` int(11) NOT NULL,
  `notes` varchar(100) NOT NULL,
  PRIMARY KEY (`pid`),
  KEY `posts` (`posts`)
  KEY `comments` (`comments`)

The table has over 1 million rows, MariaDB version 10.0.20

Now I want to perform an update and set a notice on one post that has more comments than posts

UPDATE posts SET notes = 'more comments' WHERE posts < comments

Now this query takes about 2-4 seconds to complete.

I suppose I'm missing an index or is it necessary to rewrite the query?

When I do queries with a fixed comparison

UPDATE posts SET notes = 'more comments' WHERE posts > 1000

the query runs fast.

  • Note that you have one index in comments but posts hasn't.
    – oNare
    Jul 22, 2015 at 16:04
  • So, in all rows that have posts<comments you want the notes to be 'more comments'. What should be in the rest of the rows? And how often do you plan to run this update? Jul 22, 2015 at 16:09
  • just a typo, I've corrected it now. Actually this query runs very often, I'd only like to update matching rows, others should stay as they are.
    – maddo7
    Jul 22, 2015 at 16:10
  • 1
    how many records meet the criteria posts < comments and how many records meet the criteria posts > 1000? Jul 22, 2015 at 16:19

1 Answer 1


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:

  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.

If the '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):

  DROP COLUMN notes,
      CASE WHEN columns > posts THEN 'more comments'
           WHEN columns = 0     THEN 'no comments'
           WHEN columns = posts THEN 'comments = posts'
           ELSE 'less comments'
  • as I'm using this design for a while now I'm encountering deadlocks when used in a queue and there are queries that update the comment_posts_diff and use it in a where statement, see dba.stackexchange.com/questions/114708/…
    – maddo7
    Sep 10, 2015 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.