Меню
Что нового?

Выборка последних комментариев. Прямой эфир.

sage

Новичок
Выборка последних комментариев. Прямой эфир.

Есть таблица comments (887 записей), в которой idobject - это id статьи или обсуждения, а object_type - 3 (статья) или 4 (обсуждение). Требуется реализовать прямой эфир (можно глянуть, например, на хабре): выбрать 20 последних (по дате добавления) комментариев из статей и обсуждений. При этом из каждой статьи и обсуждения выбирается только 1 последний комментарий. Что у меня получилось:

[sql]
SELECT `idcomments`, `idusers`, `idobject`, `object_type`
FROM `comments`
WHERE `idstatus` = 1 AND `object_type` IN (3, 4) AND `rating` >= 0 AND `idcomments` = ANY (SELECT MAX(`idcomments`) FROM `comments` GROUP BY `idobject`)
ORDER BY `added` DESC
LIMIT 20
[/sql]

Запрос работает верно, но выполняется 6 секунд (phpMyAdmin). Есть какие-нить способы оптимизации? Мб, кто-то делал раньше - как вы это реализовывали? Всем заранее спасибо.
 
С.

С.

Продвинутый новичок
Re: Выборка последних комментариев. Прямой эфир.

А так:

[sql]
SELECT DISTINCT `idcomments`, `idusers`, `idobject`, `object_type`
FROM `comments`
WHERE `idstatus` = 1 AND `object_type` IN (3, 4) AND `rating` >= 0
ORDER BY `added` DESC
LIMIT 20
[/sql]
 

sage

Новичок
дистинкт не решает задачу, потому что связка `idcomments` и `idusers` уникальная для любого комментария
 

sage

Новичок
1.
[sql]
CREATE TABLE `comments` (`idcomments` mediumint(8) unsigned NOT NULL auto_increment, `idstatus` tinyint(1) unsigned NOT NULL default '1' COMMENT '0 - Удалён', `parent` mediumint(8) unsigned NOT NULL, `idusers` mediumint(8) unsigned NOT NULL, `idobject` mediumint(8) unsigned NOT NULL, `object_type` tinyint(1) unsigned NOT NULL, `text` text NOT NULL, `added` datetime NOT NULL, `rating` smallint(6) NOT NULL default '0', PRIMARY KEY (`idcomments`), KEY `idusers` (`idusers`), KEY `rating` (`rating`), KEY `idobject` (`idobject`), KEY `object_type` (`object_type`), KEY `idstatus` (`idstatus`)) ENGINE=MyISAM DEFAULT CHARSET=utf8
[/sql]

2.
[sql]EXPLAIN SELECT[/sql]
Код: 
id   select_type          table      type   possible_keys                 key         key_len    ref 	rows    Extra 
1    PRIMARY              comments   ref    rating,object_type,idstatus	  idstatus    1          const	879     Using where; Using filesort
2    DEPENDENT SUBQUERY	  comments   index  NULL	                  idobject    3          NULL	887     Using filesort
3. 5.9112 сек
4. без лимита 77
 
zerkms

zerkms

TDD infected
Команда форума
для начала, попробуй добавить составной индекс
idcomments + object_type + rating + idstatus

и замени ANY на IN
 

sage

Новичок
[sql]
SHOW CREATE TABLE `comments`
[/sql]

[sql]
CREATE TABLE `comments` (`idcomments` mediumint(8) unsigned NOT NULL auto_increment, `idstatus` tinyint(1) unsigned NOT NULL default '1' COMMENT '0 - Удалён', `parent` mediumint(8) unsigned NOT NULL, `idusers` mediumint(8) unsigned NOT NULL, `idobject` mediumint(8) unsigned NOT NULL, `object_type` tinyint(1) unsigned NOT NULL, `text` text NOT NULL, `added` datetime NOT NULL, `rating` smallint(6) NOT NULL default '0', PRIMARY KEY (`idcomments`,`object_type`,`rating`,`idstatus`), KEY `idusers` (`idusers`), KEY `rating` (`rating`), KEY `idobject` (`idobject`), KEY `object_type` (`object_type`), KEY `idstatus` (`idstatus`)) ENGINE=MyISAM DEFAULT CHARSET=utf8
[/sql]

[sql]
EXPLAIN
[/sql]
Код: 
id   select_type         table      type    possible_keys                key       key_len   ref        rows    Extra 
1    PRIMARY             comments   ref     rating,object_type,idstatus  idstatus  1         const      879     Using where; Using filesort
2    DEPENDENT SUBQUERY  comments   index   NULL                         idobject  3         NULL       887     Using filesort
составной индекс не используется
 
Wicked

Wicked

Новичок
и замени ANY на IN
Нажмите для раскрытия...
от этого dependent subquery не исчезнет :)

-~{}~ 15.07.08 11:12:

такие вещи вообще лучше всего делать на обычном FIFO в памяти.

но если хочется с базой, то попробуй
Код: 
SELECT `idcomments` , `idusers` , `idobject` , `object_type`
FROM `comments`
  INNER JOIN
  (
    SELECT
      MAX( `idcomments` )
    FROM `comments`
    WHERE `idstatus` = 1 AND `object_type` IN ( 3, 4 ) AND `rating` >= 0
    GROUP BY `idobject`
    ORDER BY `added` DESC
    LIMIT 20
  ) as `ids`
  ON (`ids`.`idcomments` = `comments`.`idcomments`)
и результаты в студию
 

sage

Новичок
0.0128 сек - результат потрясающий ) Спасибо! Правда,

1. вывод не сортируется по дате, но это решается ещё одной сортировкой по дате
2. на первый взгляд в выводе отсутствуют некоторые комментарии, которые даёт вывод из стартового поста
 
С.

С.

Продвинутый новичок
Я не верю в сложные запросы. Лучше уж два простых. Сначала
SELECT DISTINCT `idobject`, потом SELECT ... IN (...)
 
Wicked

Wicked

Новичок
Я не верю в сложные запросы. Лучше уж два простых.
Нажмите для раскрытия...
не поверишь, но я тоже так считаю :)

0.0128 сек - результат потрясающий
Нажмите для раскрытия...
и все же, хотелось бы увидеть explain :)
потому что мне все-таки кажется, что с суммарным ростом записей запрос будет работать все медленней и медленней.

2. Результатов стало меньше или просто частично другие?
 

sage

Новичок
я согласен с простыми запросами. но что даст DISTINCT, если `idcomments` + `idobject` уникальна?
[sql]
EXPLAIN SELECT `idcomments` , `idusers` , `idobject` , `object_type`
FROM `comments`
INNER JOIN (

SELECT MAX( `idcomments` ) AS `m`
FROM `comments`
WHERE `idstatus` =1
AND `object_type`
IN ( 3, 4 )
AND `rating` >=0
GROUP BY `idobject`
ORDER BY `added` DESC
LIMIT 20
) AS `ids` ON ( `ids`.`m` = `comments`.`idcomments` )
ORDER BY `added` DESC
[/sql]

Код: 
id  select_type   table       type   possible_keys                key       key_len     ref     rows    Extra 
1   PRIMARY       <derived2>  ALL    NULL                         NULL      NULL        NULL    20      Using temporary; Using filesort
1   PRIMARY       comments    ref    PRIMARY                      PRIMARY   3           ids.m   8	 
2   DERIVED       comments    ref    rating,object_type,idstatus  idstatus  1                   879     Using where; Using temporary; Using filesort
результаты частично другие
 
С.

С.

Продвинутый новичок
DISTINCT только по `idobject`. Получаешь список статей, где есть последние комменты. Вторым запросом достаешь последние комменты с этими `idobject`ами.
 

sage

Новичок
делаю

[sql]
SELECT `t1`.`idobject`
FROM `comments` `t1`
WHERE `t1`.`idstatus` =1
AND `t1`.`object_type`
IN ( 3, 4 )
AND `t1`.`rating` >=0
ORDER BY `t1`.`added` DESC
[/sql]

получаю список с дублями, отсортирован по дате верно.

[sql]
SELECT DISTINCT `t1`.`idobject`
FROM `comments` `t1`
WHERE `t1`.`idstatus` =1
AND `t1`.`object_type`
IN ( 3, 4 )
AND `t1`.`rating` >=0
ORDER BY `t1`.`added` DESC
[/sql]

результат - совершенно непонятно по какому признаку отсортированный список

-~{}~ 16.07.08 11:35:

Wicked
большое спасибо! решил задачу. немного модифицировав твой запрос :)

[sql]
SELECT `idcomments` , `idusers` , `idobject` , `object_type`
FROM `comments` `t1`
INNER JOIN (

SELECT MAX( `idcomments` ) AS `c1`
FROM `comments`
WHERE `idstatus` =1
AND `object_type`
IN ( 3, 4 )
AND `rating` >=0
GROUP BY `idobject`
ORDER BY MAX( `added` ) DESC
LIMIT 20
) `t2` ON ( `t1`.`idcomments` = `t2`.`c1` )
[/sql]
 
Войдите или зарегистрируйтесь для ответа.
Сверху