Optimize MAX or ORDER BY column DESC with LIMIT 1

Question

I have a scenario where I need to Insert a new record and increase a sub-id. The Query is very slow. Exist any better Solutions for this?

Table

CREATE TABLE `link_` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `duid` bigint(20) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  KEY `duid` (`duid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Solution 1

INSERT INTO link_ (duid, domain_id)
SELECT
    IFNULL(MAX(duid), 0) + 1 AS duid, 5
FROM
link_
WHERE domain_id = 5;

Solution 2

INSERT INTO link_ (duid, domain_id)
SELECT
    IFNULL(duid, 0) + 1 AS duid, 5
FROM
    link_
WHERE domain_id = 5
ORDER BY duid DESC
LIMIT 1;

score 2 · Accepted Answer · edited Jun 15 '20 at 09:05

You may need to redesign this table, but I think you will love to do so. Why ???

Did you know that MyISAM supports having an auto_increment id grouped to a key ?

In the MySQL Documentation for using auto_increment under the subheading MyISAM Notes:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

MyISAM will do all the heavy lifting of assigning the next du_id for each domain_id

I discussed this a few times before

Aug 28, 2013 : How to go about modeling the main object of relationship?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin (Sample Code)
Jun 10, 2012 : MySQL get next unique value without auto increment
Apr 21, 2012 : How can you have two auto-incremental columns in one table?

HOW TO MAKE USE OF THIS

Change the table's design to look like this:

CREATE TABLE `link_` (
  `duid` bigint(20) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`domain_id`,`du_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You would eliminate id (saving 8 bytes per row) and remove a needless index.

Then, your INSERTs would look like this:

INSERT INTO link_ (domain_id) VALUES (5);

That's it !!! All the auto_increment stuff is handled for you !!!

Please look over the sample code I mentioned.

GIVE IT A TRY !!!

score 1 · Answer 2 · answered Feb 07 '18 at 04:01

1

ALTER TABLE `link_`
ADD INDEX (`domain_id`,`duid`)

With this index your first query should work well. You may have to switch your engine to InnoDB, but I'm not sure, as I have no experience with MyISAM.

answered Feb 07 '18 at 04:01

Willem Renzema

1,989
1
11
13

score 1 · Answer 3 · answered Feb 07 '18 at 18:58

Adding an index which contains both domain_id and duid should indeed speed up the query in your first solution. Moreover though, this will make one of your existing indexes unnecessary. Indexes "short-circuit" so an index on (domain_id, duid) will work just as effectively for domain_id queries as for domain_id+duid queries, but not the reverse, since duid appears second. If you think carefully about the combination of filters likely to be used in queries, you can be more efficient with creating a minimally covering set.

Optimize MAX or ORDER BY column DESC with LIMIT 1

3 Answers3

HOW TO MAKE USE OF THIS

GIVE IT A TRY !!!