1

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

CREATE TABLE IF NOT EXISTS `posts` (
  `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`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306;

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 '15 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? – ypercubeᵀᴹ Jul 22 '15 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 '15 at 16:10
  • 1
    how many records meet the criteria posts < comments and how many records meet the criteria posts > 1000? – Remus Rusanu Jul 22 '15 at 16:19
3

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

or:

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):

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'
      END
    VIRTUAL ;
  • 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 '15 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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