我将所有表都存储在myISAM中，但是当我长时间运行更新作业时，表级锁定开始使我丧命。我将主表转换为InnoDB，现在我的许多查询都花了1分钟以上的时间来完成，它们几乎是在myISAM上即时完成的。它们通常卡在Sorting result台阶中。我做错什么了吗？

例如 ：

SELECT * FROM `metaward_achiever` 
 INNER JOIN `metaward_alias` ON (`metaward_achiever`.`alias_id` = `metaward_alias`.`id`) 
 WHERE `metaward_achiever`.`award_id` = 1507  
 ORDER BY `metaward_achiever`.`modified` DESC 
 LIMIT 100

现在大约需要90秒。这是描述：

+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
| id | select_type | table             | type   | possible_keys                                         | key                        | key_len | ref                             | rows  | Extra                       |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | metaward_achiever | ref    | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4       | const                           | 66424 | Using where; Using filesort | 
|  1 | SIMPLE      | metaward_alias    | eq_ref | PRIMARY                                               | PRIMARY                    | 4       | paul.metaward_achiever.alias_id |     1 |                             | 
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+

现在，我查询的TONS似乎卡在了“排序结果”步骤中：

mysql> show processlist;
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host      | db   | Command | Time | State          | Info                                                                                                 |
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 460568 | paul | localhost | paul | Query   |    0 | NULL           | show processlist                                                                                     | 
| 460638 | paul | localhost | paul | Query   |    0 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
| 460710 | paul | localhost | paul | Query   |   79 | Sending data   | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
| 460722 | paul | localhost | paul | Query   |   49 | Updating       | UPDATE `metaward_alias` SET `modified` = '2009-09-15 12:43:50', `created` = '2009-08-24 11:55:24', ` | 
| 460732 | paul | localhost | paul | Query   |   25 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

为什么这个简单的更新会停留49秒？

如果有帮助，请参见以下架构：

| metaward_alias | CREATE TABLE `metaward_alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `string_id` varchar(255) DEFAULT NULL,
  `shortname` varchar(100) NOT NULL,
  `remote_image` varchar(500) DEFAULT NULL,
  `image` varchar(100) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  `md5` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `string_id` (`string_id`),
  KEY `metaward_alias_user_id` (`user_id`),
  KEY `metaward_alias_type_id` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=858381 DEFAULT CHARSET=utf8 |

| metaward_award | CREATE TABLE `metaward_award` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `string_id` varchar(20) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` longtext NOT NULL,
  `owner_points` int(11) NOT NULL,
  `url` varchar(500) NOT NULL,
  `remote_image` varchar(500) DEFAULT NULL,
  `image` varchar(100) NOT NULL,
  `parent_award_id` int(11) DEFAULT NULL,
  `slug` varchar(110) NOT NULL,
  `true_points` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `string_id` (`string_id`),
  KEY `metaward_award_owner_id` (`owner_id`),
  KEY `metaward_award_parent_award_id` (`parent_award_id`),
  KEY `metaward_award_slug` (`slug`),
  KEY `metaward_award_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=122176 DEFAULT CHARSET=utf8 |

| metaward_achiever | CREATE TABLE `metaward_achiever` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `award_id` int(11) NOT NULL,
  `alias_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `metaward_achiever_award_id` (`award_id`),
  KEY `metaward_achiever_alias_id` (`alias_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77175366 DEFAULT CHARSET=utf8 |

这些在我的my.cnf中

innodb_file_per_table
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 16M
innodb_flush_method=O_DIRECT