Optimize MAX or ORDER BY column DESC with LIMIT 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;
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 !!!