Все-таки требуется помощь по MySQL

Все тормоза сегодняшнего утра были из-за запросов такого вида (как я их искал - отдельная песня):

/*1*/ SELECT DISTINCT(ib_posts.author_id), ib_topics.* FROM ib_topics LEFT JOIN ib_posts ON (ib_topics.tid=ib_posts.topic_id AND ib_posts.author_id=1) WHERE ib_topics.forum_id=3 and ib_topics.approved=1 and (ib_topics.pinned=1 or ib_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30

Напомню, что ip_posts - это вся база сообщений, ~300тыс. записей, ~500Мб размером.

После всех оптимизаций и индексаций EXPLAIN выдаёт такое (ох, и разопрёт же сейчас топик):

1. +----+-------------+-----------+------+-------------------------------------------+------------------------+---------+---------------------------+------+----------------------------------------------+
2. | id | select_type | table     | type | possible_keys                             | key                    | key_len | ref                       | rows | Extra                                        |
3. +----+-------------+-----------+------+-------------------------------------------+------------------------+---------+---------------------------+------+----------------------------------------------+
4. |  1 | SIMPLE      | ib_topics | ref  | forum_id,last_post,forum_id_last_post_bal | forum_id_last_post_bal |       2 | const                     | 2929 | Using where; Using temporary; Using filesort |
5. |  1 | SIMPLE      | ib_posts  | ref  | topic_id,author_id,topic_id_index_bal     | topic_id               |       7 | FORUM.ib_topics.tid,const |    3 | Using where; Using index                     |
6. +----+-------------+-----------+------+-------------------------------------------+------------------------+---------+---------------------------+------+----------------------------------------------+

Запрос такого рода приводит к образованию временного файла на ~650Мб. Пока запрос не выполнится - следующие стоят в очереди. Запросы такого типа шли постоянно. Сейчас просто отрубил соответствующую фичу форума. Посмотрим, как он дальше будет жить

Рома, не знаю поможет ли в твоём случае, но я всегда пользуюсь таким правилом - вместо того, чтобы делать фильтры (типа WHERE) на уже готовой JOIN выборке, я делаю позапросы с WHERE, а уже потом делаю JOIN. Это уменьшает время JOIN операции, которая часто имеет время O(N²). Иначе говоря, сначала сделай все позапросы вида SELECT...WHERE, а потом уже делай LEFT JOIN.

Rada, 16.02.2004 20:47:21 :

Иначе говоря, сначала сделай все позапросы вида SELECT...WHERE, а потом уже делай LEFT JOIN.

 

Ничего не понял. По синтаксису можно только "SELECT... JOIN ... WHERE". Он, кстати, если по Explain посмотреть, сам сперва всё по WHERE отбирает и только с остатками дальше работает. Например, в вышеприведённом примере у него остаётся только ~3тыс. строк из одной таблицы и 3 - из другой.

Или ты имел в виду запрос на два разбить?

В общем, уточни Пример из вышеприведённого запроса слепить можешь?

Ром, это для страницы показа самых последних новостей?

Какая версия MySQL?

А файл - это от ORDER - плюс еще DISTINCT может вносить лепту.
ib_posts как в предыдущем топике? А схему ib_topics можно? Притопаю домой помозгую немного.

Mishka, 17.02.2004 23:37:17 :

Ром, это для страницы показа самых последних новостей?

 

Это страницы форума, где показывается ещё последний ответ (пользователь, заголовок).

>Какая версия MySQL?

4.1.1-alpha

>А файл - это от ORDER - плюс еще DISTINCT может вносить лепту.

Сортировка выполняется уже после того, как получен результат запроса и потому последующие запросы не блокирует. Сам видел пару раз в списке процессов "сортирует в файле"- они никому совершенно не мешали.

А вот DISTINCT - не знаю, что такое, зачем надо, и как работает

>ib_posts как в предыдущем топике?

Ага.

>А схему ib_topics можно? Притопаю домой помозгую немного.

1. CREATE TABLE ib_topics (
2.   tid int(10) NOT NULL auto_increment,
3.   ubb_topic varchar(3) default NULL,
4.   title varchar(85) NOT NULL default '',
5.   description varchar(85) default NULL,
6.   state varchar(2) default NULL,
7.   posts int(10) default NULL,
8.   starter_id mediumint(8) NOT NULL default '0',
9.   start_date int(10) default NULL,
10.   last_poster_id mediumint(8) NOT NULL default '0',
11.   last_post int(10) NOT NULL default '0',
12.   icon_id tinyint(2) default NULL,
13.   starter_name varchar(10) default NULL,
14.   last_poster_name varchar(10) default NULL,
15.   poll_state varchar(2) default NULL,
16.   last_vote int(10) default NULL,
17.   views int(10) default '0',
18.   forum_id smallint(5) NOT NULL default '0',
19.   approved tinyint(1) NOT NULL default '0',
20.   author_mode tinyint(1) default NULL,
21.   pinned tinyint(1) NOT NULL default '0',
22.   moved_to varchar(21) default NULL,
23.   rating text,
24.   total_votes int(5) NOT NULL default '0',
25.   rating_total int(11) NOT NULL default '0',
26.   rating_total_voters int(11) NOT NULL default '0',
27.   rating_voters text NOT NULL,
28.   PRIMARY KEY  (tid),
29.   KEY forum_id (forum_id,approved,pinned),
30.   KEY last_post (last_post)
31. ) TYPE=MyISAM DEFAULT CHARSET=utf8;

2 Рома: приду домой, напишу, мне надо кое-что из синтаксиса вспомнить.

И еще вопрос, если LEFT JOIN идет по
ib_topics.tid=ib_posts.topic_id AND ib_posts.author_id=1
и author_id уже известен, а topic_id будет выбран, то зачем этот JOIN - для того, чтобы проверить, если топик не пустой?

[quote|Balancer, 17.02.2004 23:47:55 :][А вот DISTINCT - не знаю, что такое, зачем надо, и как работает
[/quote]

это обозначает, что ты хочешь выбрать записи без повторений. Т.е., если у тебя два одинаковых кортежа, то выдан в качестве ответа будет только один. Это приводит к тому, что этого нельзя сделать сокращенную работу - сначала выборка всех результатов, потом, как правило сортировка с удалением повторов, а лишь потом тебе вернут первые 30 рядков.

" ib_posts.author_id=1" - Это частный случай. Вообще - там переменная Это я свои сообщения искал для теста.

В чем я вижу причину столь больших файлов. Таблица топиков небольшая - 3000-5000 топиков. НО... когда ты делаешь JOIN, то из таблицы постов ты получаешь все входы автора, для данного топика - т.е., если автор постил 100 раз, то ты и получишь 100 записей. Что получаем, 3000 записей умножаем на 100 и получаем 300000 - по правилам реляционной алгебры. Эти 300000 и будем сортировать с удалением повторов.

Кстати, если last_posted и post_date у нас одно и тоже число, время, то добавив условие в JOIN last_posted = post_date, можно решить проблему (если только этот JOIN нужен вообще).

Balancer, 17.02.2004 23:57:47 :

" ib_posts.author_id=1" - Это частный случай. Вообще - там переменная Это я свои сообщения искал для теста.

 

Переменная - это перловская или пхпэшная?

А как выглядит истинный виновник (SQL)?

Mishka, 18.02.2004 00:06:39 :

А как выглядит истинный виновник (SQL)?

 

Гм. Кто?

Ну тот SQL, на базе которого ты свои посты искал. Так сказать, прародитель частного случая.

Mishka, 18.02.2004 00:22:42 :

Ну тот SQL, на базе которого ты свои посты искал. Так сказать, прародитель частного случая.

 

Наверное, мне надо пару часов поспать. Я тебя совсем не понимаю Ты код имеешь в виду, что ли?

1. $query = "/*1*/ SELECT DISTINCT(ib_posts.author_id), ib_topics.* FROM ib_topics
2.                       LEFT JOIN ib_posts ON
3.                       (ib_topics.tid=ib_posts.topic_id AND ib_posts.author_id=".$ibforums->member['id'].")
4.                       WHERE ib_topics.forum_id=".$this->forum['id']."
5.                       and ib_topics.approved=1
6.                       and (ib_topics.pinned=1 or ib_topics.last_post > $Prune)";

(комментарий - это у меня уже воткнут, когда я искал какие же из запросов затыкают систему).

Кстати, проблема именно в залочке. Когда форумы Авиабазы жутко тормозят, мало того, что "голый" HTML на ней летает (WWW.GOROD-SPAL.RU · Фотография. Графический дизайн. Веб-дизайн, например), так ещё и другие сайты на том же MySQL, но с другими таблицами - работают обычно нормально (Action Controller: Exception caught. Нет, конечно, когда своп забит по уши, тормозит уже всё, но это последнее время нечасто бывает, я сильно урезал расходы памяти и на httpd и на mysqld.

Один хрен - поскольку и topic_id и author_id известны на момент JOIN - похоже, что это проверка на существование. Надо вспомнить в точности как работет JOIN - здесь я немного забыл - точнее я его путаю все время с OUTER JOIN. Интересно, а EXISTS работает в MySQL?

Хм, сейчас покапался в описаниях стандартов и MS SQL и нашел вот чего:

Microsoft® SQL Server™ 2000 uses these SQL-92 keywords for outer joins specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN


RIGHT OUTER JOIN or RIGHT JOIN


FULL OUTER JOIN or FULL JOIN

 

С этих позиций, тот самый JOIN на фиг не нужен, т.к. OUTER JOIN возвращает NULL для несуществующих записей. Поэтому проверь, если это существенно для дальнейшей программы, то надо думать, а, если нет, то JOIN надо херить.

Пока вот такое предложение:

1. $query = "/*1*/ SELECT DISTINCT(ib_posts.author_id), ib_topics.* FROM ib_topics
2.                       LEFT JOIN ib_posts ON
3.                       (ib_topics.tid=ib_posts.topic_id AND ib_posts.author_id=".$ibforums->member['id'].")
4.                       WHERE ib_topics.forum_id=".$this->forum['id']."
5.                       and ib_topics.approved=1
6.                       and (ib_topics.pinned=1 or ib_topics.last_post > $Prune)";

Бьем на два:

1. $query = "/*1*/ SELECT ib_topics.* FROM ib_topics
2.                       WHERE ib_topics.forum_id=".$this->forum['id']."
3.                       and ib_topics.approved=1
4.                       and (ib_topics.pinned=1 or ib_topics.last_post > $Prune)";
5.  
6. $query1 = "/*2*/ SELECT DISTINCT author_id, tid
7.                       FROM ib_posts
8.                       WHERE ib_topics.tid IN ($list)
9.                         AND ib_posts.author_id=".$ibforums->member['id']."

Переменную $list подготовить надо из $query - это список forum_id через запятую. Результаты в хэш по tid. Соответственно, потом из хэша можно доставать, когда нужен будет.

Посмтри все же еще на даты - если они одинаковы, то добавив их в JOIN можно избежать этой нервотрепки.

Примерно так я сделал бы свой запрос

1. Q1 =    SELECT ib_posts.author_id, ib_posts.topic_id
2.         FROM ib_posts
3.         WHERE ib_posts.author_id=1
4.  
5. Q2 =    SELECT ib_topics.*
6.         FROM ib_topics
7.         WHERE ib_topics.forum_id=3 AND ib_topics.approved=1 AND (ib_topics.pinned=1 OR ib_topics.last_post > 0)
8.  
9. Q3 =    SELECT DISTINCT(Q1.author_id), Q2.*
10.         FROM Q2
11.         LEFT JOIN Q1 ON Q2.tid=Q1.topic_id
12.         ORDER BY Q2.pinned DESC, Q2.last_post DESC LIMIT 0, 30

Насчёт того, что первое выполняется - WHERE или JOIN - зависит от движка ИМХО, хотя раз EXPLAIN выдаёт хронологию действий, а не просто логический результат (типа сколько строк их каждой таблицы пересеклось), то может ты и прав - улучшения может и не быть. Но я обычно явно разбиваю запросы на подзапросы, чтобы быть уверенным, в том, в каком порядке производятся множества.

Mishka, 18.02.2004 01:40:32 :

С этих позиций, тот самый JOIN на фиг не нужен, т.к. OUTER JOIN возвращает...

 

Нет, такой конструкции в MySQL, вроде, нет. Разве что в 5.0

Mishka, 18.02.2004 02:04:08 :

Переменную $list подготовить надо из $query - это список forum_id через запятую. Результаты в хэш по tid. Соответственно, потом из хэша можно доставать, когда нужен будет.

 

Погоди. "WHERE ib_topics.tid IN ($list)".

tid = topic_id, идентификатор ветки, а forum_id - номер форума. Это разные вещи.

Я интепретирую

1. FROM ib_topics
2.                       LEFT JOIN ib_posts ON
3.                       (ib_topics.tid=ib_posts.topic_id AND ib_posts.author_id=".$ibforums->member['id'].")
4.                       WHERE ib_topics.forum_id=".$this->forum['id']."
5.                       and ib_topics.approved=1
6.                       and (ib_topics.pinned=1 or ib_topics.last_post > $Prune)";

как
1. Выбрать из таблицы ib_post запись, у которой forum_id задан, и одно из двух - либо топик закреплен или/либо дата последнего поста не позже указанной. Заметь, что здесь выбираются и topic_id, и forum_id.
2. Выбрать из таблицы ib_posts все записи (одно поле будет возвращено) для которых задан автор и tid, причем tid задан набором выбранных записей из ib_topics. Иными словами - выбираем только те записи, которые соответствуют выбранному форуму и всем топикам, что нас интересуют. Это действие идет вторым, т.к. использован не PLAIN JOIN, a LEFT JOIN - что является подсказкой движку - сначала первую таблицу просмотреть, а потом вторую.
3. Построить алгебраическое произведение - по условию JOIN-a из таблицы ib_topics вернеться целая туча записей для каждого топика - ровно столько, сколько указанный автор постил туда, т.к. по дате никто не смотрит. Поэтому, если топик горячий, то получим пару сотен author_id - теперь вместо одно отобранной записи мы получим пару сотен - все одинаковые. Вот почему и нужен DISTINCT.
4. Теперь пришла очередь ORDER и DISTINCT - сортируем с удалением дубликатов.
5. Выдаем первые 30 записей.

Вот поэтому я и использовал tid, а не forum_id. Это я пробовал эммулировать JOIN.

Rada, 18.02.2004 05:20:31 :

Насчёт того, что первое выполняется - WHERE или JOIN - зависит от движка ИМХО,

 

Да, хотя, вроде по стандарту, использование такой конструкции показывает движку, что человек хочет, чтобы последний шел указанным путем.

хотя раз EXPLAIN выдаёт хронологию действий, а не просто логический результат (типа сколько строк их каждой таблицы пересеклось), то может ты и прав - улучшения может и не быть.

 

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

Но я обычно явно разбиваю запросы на подзапросы, чтобы быть уверенным, в том, в каком порядке производятся множества.

 

Здесь надо только принять во внимание, что надо не всех посты одного автора, а все посты в данном топике или топиках - поэтому и использовал IN.

Пока получил большое облегчение (хотя и не такое, как ожидалось), вынеся исходники и тело сообщения в отдельные таблицы. Но по сабжевому запросу - всё равно тормозит. Всё равно отключен