Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

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

Страницы: < 1 2 3
Piter © (2006-08-08 22:32:00)
Очень интересно, почему такой запрос не нехилой машине выполняется порядка 40 секунд:

SELECT p.id, p.name, p.title, p.added_date, p.added_time, ca.name AS category_name, COUNT(f.publication_id) AS count FROM cb_publications AS p LEFT JOIN cb_categories AS ca ON (p.category_id = ca.id) LEFT JOIN cb_favorites AS f ON (p.id = f.publication_id) GROUP BY p.id HAVING count > 0 ORDER BY count DESC LIMIT 10;

Записей в cb_publications: 7644
Записей в cb_categories: 9
Записей в cb_favorites: 5257

База MySQL.

CREATE TABLE IF NOT EXISTS `cb_publications` ( `id` int(10) unsigned NOT NULL auto_increment, `category_id` int(10) unsigned NOT NULL default '0', `rubric_id` int(10) unsigned NOT NULL default '0', `user_id` int(10) unsigned NOT NULL default '0', `name` varchar(255) NOT NULL default '', `unixtime` int(10) unsigned NOT NULL default '0', `added_date` date NOT NULL default '0000-00-00', `added_time` time NOT NULL default '00:00:00', `activated_unixtime` int(10) unsigned NOT NULL default '0', `is_active` tinyint(3) unsigned NOT NULL default '0', `is_moderated` tinyint(3) unsigned NOT NULL default '0', `front_page` tinyint(3) unsigned NOT NULL default '0', `title` varchar(100) NOT NULL default '', `content` text NOT NULL, `poll_question` varchar(255) NOT NULL default '', `source` varchar(255) NOT NULL default '', `source_url` varchar(255) NOT NULL default '', `views` int(10) unsigned NOT NULL default '0', `rating` float unsigned NOT NULL default '0', `rating_votes` int(10) unsigned NOT NULL default '0', `comments_count` int(10) unsigned NOT NULL default '0', `keywords` varchar(255) NOT NULL default '', `allow_comments` tinyint(3) unsigned NOT NULL default '1', PRIMARY KEY (`id`), KEY `front_page` (`front_page`), KEY `is_active` (`is_active`), KEY `is_moderated` (`is_moderated`), KEY `category_id` (`category_id`), KEY `rubric_id` (`rubric_id`), KEY `user_id` (`user_id`), KEY `keywords` (`keywords`), KEY `name` (`name`,`added_date`) ) TYPE=MyISAM AUTO_INCREMENT=1226 ;

CREATE TABLE IF NOT EXISTS `cb_categories` ( `id` int(10) unsigned NOT NULL auto_increment, `position` smallint(5) unsigned NOT NULL default '0', `name` varchar(255) NOT NULL default '', `title` varchar(100) NOT NULL default '', `description` text NOT NULL, `adv_title` varchar(100) NOT NULL default '', `adv_text` text NOT NULL, `adv_show` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) TYPE=MyISAM AUTO_INCREMENT=16 ;

CREATE TABLE IF NOT EXISTS `cb_favorites` ( `user_id` int(10) unsigned NOT NULL default '0', `publication_id` int(10) unsigned NOT NULL default '0', `unixtime` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`user_id`,`publication_id`) ) TYPE=MyISAM;

Sam Stone (2006-08-09 10:57:00) [41]
Индексы сделать?..

evvcom (2006-08-09 08:55:00) [42]
Кроме того, количество считается по фаворитам, сортируется и по нему же отсекается (LIMIT). Так какой смысл джойнить cb_publications (7644) и cb_favorites (5257), а потом оставлять только 10 записей, если можно сразу из фаворитов отобрать 10 записей и уже их сджойнить с публикациями?! Это существенное сокращение потерь времени.

Sergey13 (2006-08-09 08:18:00) [43]
Я бы JOIN-ы убрал, ибо все равно 0 отсекаются HAVING-ом

SELECT p.id, p.name, p.title, p.added_date, p.added_time,
ca.name AS category_name,
COUNT(f.publication_id) AS p_count
FROM cb_publications AS p, cb_categories AS ca, cb_favorites AS f
where p.category_id = ca.id and p.id = f.publication_id
GROUP BY p.id, p.name, p.title, p.added_date, p.added_time, ca.name
ORDER BY p_count DESC
LIMIT 10;

Piter (2006-08-09 02:26:00) [44]
Johnmen © (09.08.06 1:12) [3]
Не понял!? У тебя же count>0 ограничение!

я в SQL очень слаб. Но объясню на пальцах, что нужно - так думаю будет понятнее, чем абстрактные таблицы.

Итак, есть таблица cb_publications- хранит информацию о публикациях (текст публикации, дату, ID автора публикации, заголовок и все такое прочее).

Публикацию пользователи могут добавлять в свой профиль, в избранное. Хранится это уже в таблице: cb_favorites, состоящей из USER_ID пользователя и publication_id заметки. Что соответствует тому, что у такого-то пользователя такая-то публикация в закладках.

Соответственно, данный запрос составляет рейтинг публикаций по количеству закладок пользователей.

Посмотреть это можно тут: http://ss.sovietservers.com/dc/statistics.php(страница может открываться долго, около минуты). Смотри там таблицу: "Рейтинг публикаций по количеству закладок"

Так вот уже точно имеется 10 публикаций, у которых этот COUNT (то есть, количество закладок на эту публикацию) больше нуля.

Поэтому HAVING вроде как можно убрать?

Как еще ускорить этот запрос?

Johnmen (2006-08-09 01:12:00) [45]

> Piter © (09.08.06 01:02) [2]
> поскольку есть точно 10 записей с count > 10, то значит HAVING можно убрать.

Не понял!? У тебя же count>0 ограничение!

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

Piter (2006-08-09 01:02:00) [46]
Johnmen © (09.08.06 0:52) [1]

и это при десятке тысяч записей? Ничего себе... Ведь есть таблицы по миллиону записей!

HAVING count > 0 ORDER BY count DESC LIMIT 10;

поскольку есть точно 10 записей с count > 10, то значит HAVING можно убрать. Это заметно может ускорить запрос?

Как можно еще оптимизировать?

Johnmen © (09.08.06 0:52) [1]
А вот лимит - это ерунда

да просто больше 10 записей не нужно, это статистика - топ.

Johnmen (2006-08-09 00:52:00) [47]
Потому, что два внешних соединения (что само по себе тяжёлая вещь), сдобренные группировкой с ограничением на группу (тоже не из лёгких).
Дело усугубляется утяжелённой сортировкой, ибо надо получить все записи для неё, а не 10, как может показаться...
А вот лимит - это ерунда.

Страницы: < 1 2 3

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »