Форум пользователей MySQL

zosiazija · 28.03.2011 12:02:30

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

Есть большая таблица с записями пользователей, InnoDB: 11 820 627 (7.5GB)
Так же есть таблица друзей, кто с кем дружит.

Нужно показать последние записи друзей (как лента) и отсортировать их так, чтобы последние были вверху.

SELECT serv, posts.id AS postid, user_id, title, posttext, ..... 

FROM posts FORCE INDEX(id)

LEFT JOIN followers ON posts.user_id = followers.following

JOIN users ON users.id = followers.following

WHERE followers.uid=23760 

ORDER BY posts.id DESC LIMIT 0, 10;

запрос выполняется особенно медленно, если у человека мало друзей,
т.е. у followers.uid = 3 их, скажем 5 или 7.

в таблице post ещё индекс на user_id
в followers, индекс на uid,following и на uid

спасибо за помощь.

paulus · 28.03.2011 21:15:26

А зачем Вы пишете левое объединение, а потом фиксируете поле в правой таблице?
И покажите EXPLAIN, пожалуйста

Есть ощущение, что FORCE INDEX тут лишний, а вот индекса по (user_id, id) не хватает.

zosiazija · 31.03.2011 12:37:30

вот что у меня получилось:

индекс uiddate (user_id,date)

SELECT STRAIGHT_JOIN serv, posts.id AS postid, title, .....

FROM posts

RIGHT JOIN followers ON followers.following=posts.user_id

JOIN users ON users.id=posts.user_id

WHERE followers.uid=23760 

ORDER BY posts.date DESC LIMIT 0,10;

вроде не тормозит

а если у пользователя мало друзей (видимо индекс работает не так, как нужно), то разбиваем с UNION:

(SELECT serv, .....  

FROM posts 

LEFT JOIN users ON users.id=posts.user_id

WHERE user_id=23760 ORDER BY posts.date DESC LIMIT 10)

UNION ALL ...

А вот подскажите, пожалуйста, как избавится от range? Очень долго из той же таблицы выполняется вот такой запрос:

SELECT *, date_format(date, '%d.%m.%y') AS fullday, date_format(date, '%H:%i') AS time 

FROM posts

WHERE user_id='16615' AND date BETWEEN '2010-12-01' AND '2010-12-31' 

ORDER BY date DESC;

EXPLAIN:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra

1     SIMPLE     posts     range     uiddy,date,uiddate     uiddate     12     NULL     3016     Using where

Т.е. выбрать все записи за определенный месяц.

Ещё почему то иногда тормозят простые запросы по индексу uiddate (user_id, date), где большой LIMIT

SELECT id,user_id, .... 

FROM posts WHERE user_id='24828' 

ORDER BY date DESC LIMIT 7475, 25;

EXPLAIN:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra

1     SIMPLE     posts     ref     uiddy,uiddate     uiddate     4     const     10470     Using where

paulus · 02.04.2011 15:12:55

user_id — число, зачем Вы ставите кавычки? Структуру таблички покажите,
есть ли NULL?

Из-за того, что есть Using where — каждая полученная строка проверяется
дополнительно на соответствие условиям. Поэтому при больших LIMIT нельзя
сразу выбрать нужные строки по индексу (просто не понятно, какие строки
надо выбрать); нужно понять, зачем используется Using where. Как правило,
это неправильные типы данных или NULL-столбцы там, где по смыслу NULL
нет.

zosiazija · 04.04.2011 12:46:28

Вот структура таблицы:

CREATE TABLE IF NOT EXISTS `posts` (

  `id` int(11) NOT NULL auto_increment,

  `user_id` int(11) NOT NULL default '0',

  `serv` smallint(6) NOT NULL default '0',

  `from` int(11) NOT NULL default '0',

  `from_aid` int(11) NOT NULL default '0',

  `date` datetime NOT NULL default '0000-00-00 00:00:00',

  `type` varchar(10) NOT NULL,

  `notes` int(11) NOT NULL default '0',

  `answer` int(11) NOT NULL default '0',

  `title` text NOT NULL,

  `photo` text NOT NULL,

  `photourl` text NOT NULL,

  `draw` smallint(1) NOT NULL default '0',

  `link` text NOT NULL,

  `quote` text NOT NULL,

  `source` text NOT NULL,

  `audio` text NOT NULL,

  `video` text NOT NULL,

  `post` text NOT NULL,

  `ban` int(1) NOT NULL default '0',

  UNIQUE KEY `id` (`id`),

  KEY `uiddy` (`user_id`),

  KEY `typ` (`type`(4)),

  KEY `notes` (`notes`),

  KEY `date` (`date`),

  KEY `uiddate` (`user_id`,`date`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10991175 ;

paulus · 08.04.2011 21:11:14

Ключик uiddy лишний (является левой частью uiddate); без кавычек в запросе
пробовали?

Форум пользователей MySQL

#1 28.03.2011 12:02:30

оптимизация запроса (join)

#2 28.03.2011 21:15:26

Re: оптимизация запроса (join)

#3 31.03.2011 12:37:30

Re: оптимизация запроса (join)

#4 02.04.2011 15:12:55

Re: оптимизация запроса (join)

#5 04.04.2011 12:46:28

Re: оптимизация запроса (join)

#6 08.04.2011 21:11:14

Re: оптимизация запроса (join)

Board footer