Мастера 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;

Slym (2006-08-11 10:46:00) [1]
Piter © (10.08.06 13:35) [40]
"#1064 - You have an error in your SQL syntax
Я же заранее предупредил "Это псевдокод" и копипастом не сработает.
И привел принцип: выбираем сначала данные (подзапросом)
а уж к ним цепляем расшифровку жойним Титлы и Наймы

Sergey13 (2006-08-10 16:59:00) [2]
> [44] roottim © (10.08.06 16:46)

Таки наверное с 8i. У меня вроде тоже нет блокировки.

evvcom (2006-08-10 16:57:00) [3]

> [43] Sergey13 © (10.08.06 16:32)
> Не по 7-ке же она написана.

Не, не по 7-ке. Я видел там упоминание 8-ки.

> [44] roottim © (10.08.06 16:46)

Вероятно, так и есть. Потому я и упомянул свою версию.

roottim (2006-08-10 16:46:00) [4]
C Oracle 9-ки блокировка таблицы при отсутствии индекса по FK канула в лету

Sergey13 (2006-08-10 16:32:00) [5]
> [42] evvcom © (10.08.06 15:51)
> У меня 9.2.0.6, видимо Кайт описывал что-то более раннее.
Возможно. Хотя у меня 8.1.7. Не по 7-ке же она написана.

evvcom (2006-08-10 15:51:00) [6]
> [41] Sergey13 © (10.08.06 14:36)

Да, вижу теперь у Кайта. Попробовал [25] еще раз, но уже с 'изменением' первичного ключа. Результат тот же. Все ок. У меня 9.2.0.6, видимо Кайт описывал что-то более раннее.

Sergey13 (2006-08-10 14:36:00) [7]
> [25] evvcom © (09.08.06 15:04)

Таки нашел я где видел сие утверждение. Вернее видел это и ранее, но нашел подтверждение. У Кайта в его "Оракл для профессионалов" в главе 7 "Индексы" есть подраздел "Индексы для внешних ключей".

Piter (2006-08-10 13:35:00) [8]
попробовал:

SELECT p.id, p.name, p.title, p.added_date, p.added_time, ca.name AS category_name, MostFavor.count as count FROM (SELECT TOP 10 cb_favorites.publication_id, count(cb_favorites.publication_id) as count FROM (cb_favorites GROUP BY cb_favorites.publication_id ORDER BY count(cb_favorites.publication_id)) as MostFavor INNER JOIN cb_publications AS p ON (MostFavor. publication_id = p.id)) INNER JOIN cb_categories AS ca ON (p.category_id = ca.id) ORDER BY count(cb_favorites.publication_id) desc;

Результат:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10 cb_favorites . publication_id , count( cb_favorites . publication_id ) as cou' at line 1 "

Piter (2006-08-10 13:25:00) [9]
Slym © (09.08.06 16:57) [34]
Мой вариант не пробывал?

не, не пробовал. Меня полностью устроило ускорение в 10 раз :)

Хотя если интересно - могу попробовать...

evvcom (2006-08-09 17:11:00) [10]
> [37] ANB © (09.08.06 17:03)
> Для проверки возможности апдейта/удаления.

Конечно же. Удаления согласен полностью. Апдейта - только ключа по которому констрейнт, обычно это primary key. Неточно оформил свою мысль, т.к. удалением подобных записей мы тоже не занимаемся, а вводим дополнительное поле флага, дабы обезопасить себя от "нечаянных" действий пользователя.

ANB (2006-08-09 17:03:00) [11]

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

Неправильно думал. Для обычных констрейнтов они тоже используются. Для проверки возможности апдейта/удаления. Чтобы поругаться.

evvcom (2006-08-09 17:01:00) [12]
> [33] Piter © (09.08.06 16:49)
> И зачем придумали внешние JOIN'ы если можно в 10 раз быстрее?

Действительно! Нафига зад... бог человеку придумал, ведь через горло гланды гораздо удобнее выдирать! :-)
Затем, что весь спектр задач не ограничивается только твоей задачей.

Sergey13 (2006-08-09 16:58:00) [13]
> [33] Piter © (09.08.06 16:49)

Джоины всякие нужны
Джойны всякие важны.

(с) Михалков или Маршак (почти)
8-)

Slym (2006-08-09 16:57:00) [14]
Мой вариант не пробывал?

Piter (2006-08-09 16:49:00) [15]
Sergey13, спасибо! Вместо 45 секунд запрос стал выполняться 1,5 секунды. Правда, при одинаковых Count чуть сортировка поменялась, да хрен с ней :)

И зачем придумали внешние JOIN'ы если можно в 10 раз быстрее?

evvcom (2006-08-09 15:37:00) [16]
> [27] Piter © (09.08.06 15:15)

См. [31]. У меня Оракл.

Desdechado (2006-08-09 15:32:00) [17]
Piter © (09.08.06 15:15) [27]
> а по-моему для FK индекс создается автоматически.
В IB - да, в Оракле - нет, в MySQL - не знаю.

Slym (2006-08-09 15:30:00) [18]
ORDER BY count(cb_favorites.publication_id) desc
а строку ORDER BY count DESC; убрать

Slym (2006-08-09 15:29:00) [19]
или
SELECT p.id, p.name, p.title, p.added_date, p.added_time,
ca.name AS category_name, MostFavor.count as count
FROM
(SELECT TOP 10 cb_favorites.publication_id, count(cb_favorites.publication_id) as count
FROM (cb_favorites
GROUP BY cb_favorites.publication_id
ORDER BY count(cb_favorites.publication_id)) as MostFavor
INNER JOIN cb_publications AS p
ON (MostFavor. publication_id = p.id))
INNER JOIN cb_categories AS ca
ON (p.category_id = ca.id)
ORDER BY count DESC;

Slym (2006-08-09 15:20:00) [20]
Сначала подсчет потом красоты наводим
псевдокод не проверял:
SELECT p.id, p.name, p.title, p.added_date, p.added_time, ca.name AS category_name, MostFavor.count as count FROM (SELECT cb_favorites.publication_id, count(cb_favorites.publication_id) as count FROM (cb_favorites GROUP BY cb_favorites.publication_id ORDER BY count(cb_favorites.publication_id) LIMIT 10) as MostFavor INNER JOIN cb_publications AS p ON (MostFavor. publication_id = p.id)) INNER JOIN cb_categories AS ca ON (p.category_id = ca.id) ORDER BY count DESC;

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

Наверх

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