Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > [MySQL]Оптимизация запроса Select

Автор: Sfairet 10.12.2008, 17:22

Есть запрос в базу. Суть запроса выдавать записи по темам. Одна запись из таблицы может

принадлежать ко многим темам.
Таблица stories - содержит сами записи, имеет текстовые поля и поле sid(INT), записей порядка

200
Таблица stories_topics - привязка sid записи(поле news) к id темы(поле topic), записей примерно

400
Таблица topics_big - обеспечивает древо тем и подтем(степень вложенности - 3). Поле big -

родительская тема, small - подтема. Записей порядка 200.

Смысл sql запроса выдать все записи из stories, которые находятся в теме с id переданным

скриптом(в данном случае 4) или в любой дочерней теме, все дочерние темы записаны в таблице

topics_big (максимальная степень вложенности - 3).

Сам запрос:

SELECT SQL_CALC_FOUND_ROWS * FROM stories WHERE sid IN (
SELECT DISTINCT(s.sid) from stories as s
INNER JOIN stories_topics as top ON (s.sid = top.news)
INNER JOIN topics_big as big ON (top.topic = big.small)
LEFT JOIN topics_big as big2 ON (big.big = big2.small)
WHERE (big.big=4
OR big.small=4
OR big2.big=4
OR big2.small=4)
)
ORDER BY anchor, sid DESC limit 0, 20;

Ну и смысл поста в том, что запрос может отрабатывать и по одной минуте.
Раньше вместо него было порядка 300 мелких запросов, пока над этим думал голову сломал - в sql

не очень понимаю, но сейчас нагрузка на сервер возрасла и опять стало все тормозить.

Пример данных в прикрепленном файле.
Буду рад любой подсказке!

Автор: Akina 10.12.2008, 17:44

Структуры таблиц в терминах сервера и explain запроса - в студию.

Автор: Sfairet 10.12.2008, 19:43

Таблицы:

CREATE TABLE `stories` (
  `sid` int(11) NOT NULL auto_increment,
  `catid` int(11) NOT NULL default '0',
  `aid` varchar(30) NOT NULL default '',
  `title` text,
  `time` varchar(14) NOT NULL default '',
  `hometext` text,
  `bodytext` text NOT NULL,
  `comments` int(11) default '0',
  `counter` mediumint(8) unsigned default NULL,
  `topic` int(3) NOT NULL default '1',
  `informant` varchar(20) NOT NULL default '',
  `notes` text NOT NULL,
  `ihome` int(1) NOT NULL default '0',
  `alanguage` varchar(30) NOT NULL default '',
  `acomm` int(1) NOT NULL default '0',
  `haspoll` int(1) NOT NULL default '0',
  `pollID` int(10) NOT NULL default '0',
  `score` int(10) NOT NULL default '0',
  `ratings` int(10) NOT NULL default '0',
  `rating_ip` varchar(15) default '0',
  `associated` text NOT NULL,
  `keyword` text NOT NULL,
  `description` text NOT NULL,
  `book_name` text NOT NULL,
  `book_author` text NOT NULL,
  `anchor` char(3) NOT NULL default '1',
  PRIMARY KEY  (`sid`),
  KEY `sid` (`sid`),
  KEY `catid` (`catid`),
  KEY `counter` (`counter`),
  KEY `topic` (`topic`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=330 ;

CREATE TABLE `stories_topics` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `news` int(10) unsigned NOT NULL default '0',
  `topic` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1042 ;

CREATE TABLE `topics_big` (
  `big` int(11) NOT NULL default '0',
  `small` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

ЭКСПЛЭЙН запроса:

id    select_type                    table             type     possible_keys    key         key_len    ref    rows  Extra
1    PRIMARY                            sfairet_stories     ALL             NULL                NULL         NULL         NULL    207    Using where; Using filesort
2    DEPENDENT SUBQUERY    s                     eq_ref     PRIMARY,sid        PRIMARY   4                 func    1    Using where; Using index; Using temporary
2    DEPENDENT SUBQUERY    big                     ALL             NULL                NULL         NULL         NULL    167    Distinct
2    DEPENDENT SUBQUERY    big2                  ALL             NULL                NULL         NULL         NULL    167    Using where; Distinct
2    DEPENDENT SUBQUERY    top                     ALL             NULL                NULL         NULL         NULL    498    Using where; Distinct

P.S. у всех таблиц идет префикс sfairet_

Автор: Zloxa 11.12.2008, 10:28

Индексируйтесь по критериям объединения. (stories_topics.topic , topics_big.big, topics_big.small)
Уберите disticnt, для In он не нужен, получается лишняя сортировка.
Соединение INNER JOIN stories_topics as top - лишнее sid вы получаете из top.news.

т.е. прееписать запрос както так:

Код


SELECT SQL_CALC_FOUND_ROWS * FROM stories WHERE sid IN (
SELECT top.news from stories_topics as top
INNER JOIN topics_big as big ON (top.topic = big.small)
LEFT JOIN topics_big as big2 ON (big.big = big2.small)
WHERE (big.big=4
 OR big.small=4
 OR big2.big=4
 OR big2.small=4)
)
        ORDER BY anchor, sid DESC limit 0, 20;

Автор: Sfairet 13.12.2008, 16:22

Цитата(Zloxa @ 11.12.2008, 10:28)

Индексирование topics_big.big, topics_big.small уменьшило время запроса в среднем в 100 раз.
Среднее время стало 0,148 секунды.

Изменение запроса дало еще 2 миллисекунды.

А вот добавление индекса на stories_topics.topic замедлило запрос на 100 миллисекунд.

Вообщем лагов больше нет, ОГРОМНОЕ спасибо!=)