我有两张table：
第一：

CREATE TABLE `dialog_projects` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`creator_user_id` INT(11) UNSIGNED NOT NULL,
`add_users_allowed` INT(1) NULL DEFAULT '1',
`dlg_name` VARCHAR(200) NOT NULL,
`dlg_date` DATETIME NOT NULL,
PRIMARY KEY (`id`),
INDEX `dialog_projects_creator_user_id_ind` (`creator_user_id`),
INDEX `dialog_projects_add_users_allowed_ind` (`add_users_allowed`),
INDEX `dialog_projects_dlg_date_ind` (`dlg_date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=220094
/*!50100 PARTITION BY KEY (id)
PARTITIONS 10  */;

第二：

CREATE TABLE `dialog_users` (
`dialog_projects_id` INT(11) UNSIGNED NOT NULL,
`user_id` INT(11) UNSIGNED NOT NULL,
`num_new_msgs` INT(11) UNSIGNED NOT NULL,
`accepted` TINYINT(1) NULL DEFAULT '0',
`last_visit` DATETIME NOT NULL,
PRIMARY KEY (`dialog_projects_id`, `user_id`, `num_new_msgs`),
INDEX `dialog_projects_accepted_ind` (`accepted`),
INDEX `dialog_projects_last_visit_ind` (`last_visit`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
/*!50100 PARTITION BY HASH (dialog_projects_id + user_id + num_new_msgs)
PARTITIONS 10  */;

此查询执行大约5,5秒，但如果没有“order by du.num\u new\u msgs desc”，则需要0.005秒。如何提高速度？怎么了？

# explain

select SQL_NO_CACHE dp.id
from `dialog_users` as du 
left join `dialog_projects` as dp
 on (du.dialog_projects_id = dp.id) 
where dp.id > 300 and du.num_new_msgs > -1 
    and dp.add_users_allowed = 0 and du.user_id = 10990 

order by du.num_new_msgs desc, dp.id desc 
limit 10

下面我们来解释一下：

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "dp"    "ref"   "PRIMARY,dialog_projects_add_users_allowed_ind" "dialog_projects_add_users_allowed_ind" "5" "const" "100246"    "Using where; Using index; Using temporary; Using filesort"
"1" "SIMPLE"    "du"    "ref"   "PRIMARY"   "PRIMARY"   "8" "sport-event.dp.id,const"   "1" "Using where; Using index"

谢谢