Правильно выставить индексы MySQL

[woowap]

Здравствуйте! Помогите пожалуйста, есть таблица:

CREATE TABLE IF NOT EXISTS `msg_last` (
  `sender_id` int(10) unsigned NOT NULL COMMENT 'отправитель',
  `user_id` int(10) unsigned NOT NULL COMMENT 'получатель',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'время',
  `msg` varchar(100) NOT NULL COMMENT 'последнее сообщения в переписке',
  `new_msg` tinyint(3) unsigned NOT NULL COMMENT 'кол. новых смс',
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Делаю такой запрос:

SELECT M.sender_id
FROM `msg_last` M
LEFT JOIN `users` U ON M.sender_id = U.user_id
WHERE M.user_id = '4
ORDER BY `new_msg` DESC, `time` DESC

Explain выдает такое:

(
    [id] => 1
    [select_type] => SIMPLE
    [table] => M
    [type] => ref
    [possible_keys] => user_id
    [key] => user_id
    [key_len] => 4
    [ref] => const
    [rows] => 1
    [Extra] => Using where; Using filesort
)

Помогите избавиться от Using where; Using filesort

 

[WMix]

SHOW CREATE TABLE users;
а вообще прямые кандидаты users.user_id, msg_last.sender_id ну и msg_last.new_msg, msg_last.time

 

[woowap]

users.user_id - тут стоит уникальный PRIMARY KEY (`user_id`), msg_last.sender_id ну и msg_last.new_msg, msg_last.time ставил, но эту штука не убирается

 

[fixxxer]

штука не убирается

При малом количестве записей в таблице фулскан с сортировкой выгоднее сканирования индекса.

 

[woowap]

При малом количестве записей в таблице фулскан с сортировкой выгоднее сканирования индекса.

их будет много там, от 200 тыс.

 

[AnrDaemon]

Вот когда БУДЕТ…

 

[woowap]

От Using where не надо избавляться - это просто говорит о том, что в WHERE используется индекс.
От Using filesort избавиться легко - надо добавить индекс (user_id, new_msg, time).

легко не легко, но не избавился от них, поставил на них индексы, без изменения, на всех ставил, по одному ставил, группой ставил

 

[woowap]

@S.Chushkin,

А если не получилось - покажи как сделал.

CREATE TABLE `msg_last` (
 `sender_id` int(10) unsigned NOT NULL COMMENT 'отправитель',
 `user_id` int(10) unsigned NOT NULL COMMENT 'получатель',
 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'время',
 `msg` varchar(100) NOT NULL COMMENT 'последнее сообщения в переписке',
 `new_msg` tinyint(3) unsigned NOT NULL COMMENT 'кол. новых смс',
 KEY `user_id` (`user_id`),
 KEY `new_msg` (`new_msg`),
 KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

ALTER TABLE `msg_last` ADD INDEX ( `user_id` ) ;
ALTER TABLE `msg_last` ADD INDEX ( `time` ) ;
ALTER TABLE `msg_last` ADD INDEX ( `new_msg` ) ;

 

[AnrDaemon]

индекс (user_id, new_msg, time).

ALTER TABLE `msg_last` ADD INDEX ( `user_id` ) ;
ALTER TABLE `msg_last` ADD INDEX ( `time` ) ;
ALTER TABLE `msg_last` ADD INDEX ( `new_msg` ) ;

Как говорится, найдите десять отличий…

 

[woowap]

Как говорится, найдите десять отличий…

ALTER TABLE `msg_last` ADD INDEX ( `user_id` , `time` , `new_msg` ) ; я так тоже делал, без разницы

 

[woowap]

ALTER TABLE `msg_last` ADD INDEX ( `user_id` , `new_msg`, `time` ) ; вот так изменил порядок, и теперь нормально, хм... может кто нибудь объяснить почему так?

ALTER TABLE `msg_last` ADD INDEX ( `user_id` , `time` , `new_msg` ) ; - вот так не работает, выдает ту же ошибку

что то через чур слабо у меня с mysql(

 

[c0dex]

@woowap, имеет значение порядок полей в индексе.

 

[woowap]

@woowap, имеет значение порядок полей в индексе.

это теперь и я понял) как это в данном случае так выходит объясни если не трудно, пожалуйста

 

[c0dex]

Гугли: порядок полей в индексе mysql, ссылок море

 

[DiMA]

Можно и короче объяснять. Если есть именно такой запрос

SELECT M.sender_id
FROM `msg_last` M
LEFT JOIN `users` U ON M.sender_id = U.user_id
WHERE M.user_id = '4
ORDER BY `new_msg` DESC, `time` DESC

то под него в том же порядке, что указано в WHERE+ORDER, нужно создать индекс, т.е. (`user_id` , `new_msg`, `time`)

 

[woowap]

Можно и короче объяснять. Если есть именно такой запрос

SELECT M.sender_id
FROM `msg_last` M
LEFT JOIN `users` U ON M.sender_id = U.user_id
WHERE M.user_id = '4
ORDER BY `new_msg` DESC, `time` DESC

то под него в том же порядке, что указано в WHERE+ORDER, нужно создать индекс, т.е. (`user_id` , `new_msg`, `time`)

спасибо, вот теперь понял)
Спасибо всем огромное, очень сильно помогли, я думал это не исправить, и хотел оставить так) или же второй цикл юзать даж думал, чтоб в пхп сортировку сделать, спасибо всем!!! Вы лучшие!

 

[Breeze]

У меня другой вопрос. Нафига в запросе join если его результат нигде не используется?

 

[woowap]

У меня другой вопрос. Нафига в запросе join если его результат нигде не используется?

он используется, SELECT M.sender_id - если вы это взяли из-за этого списка, то я там вырезал, чтобы не было много кода тут

 

[woowap]

WHERE (`sender_id` = '$person_id' and `user_id` = '$user_id' and `u_act` = 'n') or (`sender_id` = '$user_id' and `user_id` = '$person_id' and `s_act` = 'n')
ORDER BY `msg_id` DESC

подскажите еще как тут выставить индекс, что то метод которые выше описали к этому не подходит у меня

 

[woowap]

аааааууууу, где вы??) помогите кто может