使用 max 和 group by 获取最新记录

我正在开发一个 php 应用程序，需要从 mysql 表中检索最新记录。下面是表格设计和样本数据。

所以标准是：

按当前 DESC 排序，然后按 start_date DESC

预期产出

我的想法如下，有没有更好的解决方案？

SELECT * 
  FROM `experience` r 
  JOIN (
        SELECT MAX(start_date) as date, 
               resume_id 
          FROM `experience` 
         WHERE present = 1 
         group by resume_id
        )r2 ON r.resume_id = r2.resume_id 
           AND r.present = 1 
           AND r.start_date = r2.date  
UNION
SELECT * 
  FROM `experience` r 
  JOIN (
        SELECT MAX(start_date) as date, 
               resume_id 
          FROM `experience` 
         WHERE present = 0 
         group by resume_id
       ) r2 ON r.resume_id = r2.resume_id 
           AND r.present = 0 
           AND r.start_date = r2.date  
 WHERE NOT EXISTS (SELECT 1 
                     FROM `experience` 
                    WHERE present = 1 
                      AND r.resume_id = resume_id
                  )

// took around 11-20 seconds to process above query on total 499,000 records

解释查询

更新：

http://sqlfiddle.com/#!9/bc5a12/1

CREATE TABLE `experience`(
       `id` int(11) NOT NULL,
       `resume_id` varchar(9) DEFAULT NULL,
       `company_name` varchar(12) DEFAULT NULL,
       `start_date` date DEFAULT NULL,
       `end_date` date DEFAULT NULL,
       `present` varchar(7) DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `experience`(
       `id`, 
       `resume_id`,  
       `company_name`, 
       `start_date`, 
       `end_date`, 
       `present`) 
 VALUES
(1, '1', 'abc', '2009-01-01', '2009-12-31', '0'),
(2, '1', 'def', '2010-01-01', '2012-12-31', '0'),
(3, '1', 'ghi', '2013-01-01', '0000-00-00', '1'),
(4, '2', 'abc', '2009-01-01', '2009-12-31', '0'),
(5, '2', 'def', '2010-01-01', '2012-12-31', '0'),
(6, '2', 'ghi', '2013-01-01', '2016-01-01', '0'),
(7, '3', 'abc', '2009-01-01', '2009-12-31', '0'),
(8, '3', 'def', '2017-01-01', '0000-00-00', '1'),
(9, '3', 'ghi', '2010-01-01', '0000-00-00', '1');

ALTER TABLE `experience`
  ADD PRIMARY KEY (`id`);