1

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;
2

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

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 !!!

  • Yeah, +5! I was planning to answer like this one! – ypercubeᵀᴹ Feb 7 '18 at 20:23
  • There is probably one single feature that MyISAM has these days that InnoDB doesn't. It's this one. – ypercubeᵀᴹ Feb 7 '18 at 20:24
  • @RolandoMySQLDBA Awesome! This was THE Solution, thank you very much! Now I love MyISAM much more. – nenad007 Feb 7 '18 at 21:39
  • @nenad007 - But try to avoid MyISAM; it is effectively deprecated by Oracle. – Rick James Feb 14 '18 at 20:06
  • @RickJames I would if I would have another engine that could deliver the same performance as MyISAM on above problem. On the actual server InnoDB took 1.5 seconds per Insert vs 30 ms of MyISAM. – nenad007 Feb 14 '18 at 22:58
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.

  • Thank you, I had them already but removed separate Index for domain_id and duid, the speed is a little faster but it takes still near 1 second (actual 3m records growing). I tried InnoDB there got Issues with Insert as did not used transactions, but I have the feeld MyISAM should be faster on this. – nenad007 Feb 7 '18 at 11:09
  • The answer here is possibly the best for InnoDB. Rolando's is better for MyISAM. MyISAM has no transaction support. – Rick James Feb 14 '18 at 20:09
1

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.

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.