Долгий MySQL запрос

#оптимизация #mysql

Данный запрос грузит примерно 7-15 секунд.

users - 10 000 записей 
tasks_done - 300 000 записей

Индексы стоят.
EXPLAIN 


SELECT `users`.`uid`, `users`.`uavatar`  
COUNT(`tasks_done`.`tdid`) as `count_tasks_done` 
FROM `tasks_done`    
INNER JOIN `users` ON (`tasks_done`.`tduid` = `users`.`uid`)   
WHERE `tasks_done`.`tdtype` = 'done' AND `users`.`uban_type` = 0 AND `users`.`udel`
= 0 AND `users`.`top` = 0    
GROUP BY `tasks_done`.`tduid`     
ORDER BY `count_tasks_done` DESC  
LIMIT 100


Таблица users:

CREATE TABLE IF NOT EXISTS users (
uid int(10) NOT NULL AUTO_INCREMENT,
ulogin varchar(15) NOT NULL,
upassword varchar(32) NOT NULL,
uemail varchar(255) NOT NULL,
temp_password varchar(32) NOT NULL, 
uemail_activated tinyint(1) NOT
NULL,   uemail_helper tinyint(1) NOT
NULL,   uvk_id int(10) NOT NULL,
uname varchar(40) NOT NULL,
ulast_name varchar(60) NOT NULL,
uip_address varchar(100) NOT NULL,
ureg_time int(10) NOT NULL,
ulast_time int(10) NOT NULL,
ugender tinyint(1) NOT NULL,
ugroup tinyint(1) NOT NULL,
upoints int(10) NOT NULL,
uban_type int(1) NOT NULL,
uban_time int(10) NOT NULL,
uban_text text NOT NULL,   uhash
varchar(100) NOT NULL,
blacklist_notif int(3) NOT NULL,
uavatar varchar(300) NOT NULL,
uagent_id int(6) NOT NULL,
uagent_avatar tinyint(2) NOT NULL,
uagent_rate_plus int(10) NOT NULL,
uagent_rate_minus int(10) NOT NULL, 
udel int(10) NOT NULL,   city
int(4) NOT NULL,   ubrowser
varchar(50) NOT NULL,   ubyear
smallint(4) NOT NULL,   complaints
int(10) NOT NULL,   account
tinyint(1) NOT NULL,
vk_time_update int(10) NOT NULL,
top smallint(1) NOT NULL,   PRIMARY
KEY (uid),   UNIQUE KEY ulogin
(ulogin),   UNIQUE KEY uemail
(uemail),   KEY ugroup (ugroup),
KEY ulast_time (ulast_time),   KEY
udel (udel),   KEY uvk_id
(uvk_id),   KEY uemail_activated
(uemail_activated),   KEY
uban_type (uban_type),   KEY
ureg_time (ureg_time),   KEY
vk_time_update (vk_time_update),
KEY top (top) ) ENGINE=InnoDB 
DEFAULT CHARSET=utf8
AUTO_INCREMENT=9352 ;

Таблица tasks_done:

CREATE TABLE IF NOT EXISTS
tasks_done (   tdid bigint(30) NOT
NULL AUTO_INCREMENT,   tduid int(10)
NOT NULL,   tdvk_id int(10) NOT
NULL,   tdtid int(10) NOT NULL,
tdurl varchar(50) CHARACTER SET
cp1251 NOT NULL,   tdtype
varchar(30) CHARACTER SET cp1251 NOT
NULL,   tdtime int(10) NOT NULL,
tdsection tinyint(2) NOT NULL,
tdread tinyint(1) NOT NULL,
unique_key varchar(60) NOT NULL,
PRIMARY KEY (tdid),   UNIQUE KEY
unique_key (unique_key),   KEY
tduid (tduid),   KEY tdtid
(tdtid),   KEY tdurl (tdurl),
KEY tdtype (tdtype),   KEY
tdsection (tdsection),   KEY
tdread (tdread),   KEY tdtime
(tdtime) ) ENGINE=InnoDB  DEFAULT
CHARSET=utf8 AUTO_INCREMENT=275732 ;
    

Ответы

Ответ 1

имхо, колонки ban_type и udel можно было бы объединить. удаленный или забаненный
в любом случае неактивный, просто еще один тип бана - удален.
что такое колонка top сложно догадаться, но такое ощущение, что индексы по всем этим
трем колонкам не приносят пользы - отсечение идет малого от большого, в итоге остается
слишком большой процент юзеров, смысла в применении таких индексов нет, чтоб потом
все равно делать по ним почти полный скан.
tdtype - строковая колонка, индекс большой по ней.
такое количество индексов приводит к тому, что оптимизатор выбирает индексы как хочет.
Надо уже пользоваться хинтами.
для данного запроса, я бы попробовал индекс по (tduid,tdtype), согласен с @Andrew
Frolov, притом оставил бы только этот индекс.
т.к. юзеры будут перебираться все равно все. в любом случае нужно будет обойти всех
чтоб получить по ним количество выполненных заданий и отсортировать по этому количеству,
а потом взять первых 100.
ЗЫ filesort по юзерам это конечно вилы.


Ответ 2

Таблицы, конечно, ужасные. Названия колонок, апострофы, куча непонятных ключей..
Но сейчас не об этом :)
Я бы для начала проверил настройки mysql. Не дефалтовые, надеюсь? 
Потом сделал бы такой индекс.
create index tasks_done_idx on tasks_done(tduid,tdtype)

Потом, наверное, стоит попробовать сделать ещё один индекс на users, чтобы не пришлось
3 индекса мерджить. 
create index user_idx on user(top,udel, ban_type)

Потом собрал бы статистику.
ANALYZE TABLE tasks_done;
ANALYZE TABLE users;