Запрос к MySQL

← →
xayam © (2009-11-20 19:49) [0]

Есть много таблиц с одинаковыми полями, есть такой запрос
(SELECT a FROM t1 WHERE ...) UNION (SELECT a FROM t2 WHERE ...) ... UNION (SELECT a FROM tn WHERE ...) ORDER BY a LIMIT 30;
у каждого столбца "a" в каждой таблице есть индекс.
Вопрос. Будут ли использоваться индексы при упорядочивании в данном запросе или нужно как-то по-другому писать, чтобы уменьшить время отклика?

← →
Омлет © (2009-11-20 20:02) [1]

> Есть много таблиц с одинаковыми полями

Что помешало всё свести в одну таблицу?

← →
xayam © (2009-11-20 20:10) [2]

> Омлет © (20.11.09 20:02) [1]
> > Есть много таблиц с одинаковыми полями
> Что помешало всё свести в одну таблицу?

во-первых очень много данных, во-вторых в каждой таблице будут храниться сообщения с определенного форума (возможен раздельный поиск по форумам), в-третьих требование протокола nntp - раздельная нумерация сообщений в группах (в форумах). Меня интересует насколько будет быстро выполняться данный запрос, если таблиц, скажем, 16, и в каждой таблице примерно по 50.000-100.000 сообщений, поиск в которых осуществляется с помощью полнотекстового индекса.

← →
antonn © (2009-11-20 21:54) [3]

> во-вторых в каждой таблице будут храниться сообщения с определенного
> форума (возможен раздельный поиск по форумам), в-третьих
> требование протокола nntp - раздельная нумерация сообщений
> в группах (в форумах).

вводятся в общую таблицу поля forum_id, group_id, добавляются к ним индексы - и легким запросом из всей кучи получаем данные по определенному форуму (или диапазону) и по определенным группам (или диапазону).
А если ты у тебя будет несколько таблиц от поиск по нескольким форумам и группам будет задачей довольно заковыристой :)

← →
antonn © (2009-11-20 22:08) [4]

могу ошибаться, но при объединении таблицы вроде бы строится временная, куда копируются объединяемые.

← →
xayam © (2009-11-20 22:22) [5]

> antonn © (20.11.09 21:54) [3]

конечно в одной было бы лучше, но я не уверен, что будет проще с нумерацией для nntp

← →
antonn © (2009-11-20 22:41) [6]

вроде бы тема про нумерацию уже была, но я все равно не понял где там вилы =)

← →
xayam © (2009-11-20 22:51) [7]

> antonn © (20.11.09 22:41) [6]
> вроде бы тема про нумерацию уже была, но я все равно не
> понял где там вилы =)

так для каждого форума должна быть отдельная нумерация т.е. придется навернуть столько же столбцов сколько и форумов на сайте (16-20). А учитывая что таблица с данными окажется огромной то будет очень много лишней информации в таблице. Или нет?

← →
Омлет © (2009-11-20 22:54) [8]

> [7] xayam © (20.11.09 22:51)
> придется навернуть столько же столбцов

Нужен будет только один дополнительный столбец с номером в рамках форума.

← →
xayam © (2009-11-20 23:05) [9]

> Омлет © (20.11.09 22:54) [8]
> Нужен будет только один дополнительный столбец с номером
> в рамках форума.

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

← →
antonn © (2009-11-20 23:17) [10]

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

заведи общую таблицу в которой храни записи о форумах, и туда клади максимальное кол-во постов для форума. При постинге увеличивай это значение, при удалении - отнимай. Для профилактики делай "переиндексацию" в кроне раз в 3 часа. По крайней мере при импорте это пойдет влет, да и при "повседневном" постинге тоже.

← →
Anatoly Podgoretsky © (2009-11-21 00:58) [11]

> Вопрос. Будут ли использоваться индексы при упорядочивании
> в данном запросе или нужно как-то по-другому писать, чтобы
> уменьшить время отклика?

Не будут, поскольку при UNION создается новая виртуальная таблица, потом она сортируется, и уже потом накладывается ограничение LIMIT - работать это будет очень медленно.

← →
Anatoly Podgoretsky © (2009-11-21 01:02) [12]

По поводу одна таблица или несколько это зависит от архитектуры, но одно точно, в случае одной таблицы придется делать сериализацию и придется сразу забыть об паралельных запросах. Никакой механизм автоинкриментных полей здесь не пойдет, могут помочь только только N генераторов или сериализация и MAX+1.
Но зато будут решены проблемы нетолько с NNTP, но и с любым другим протоколом.

← →
Anatoly Podgoretsky © (2009-11-21 01:06) [13]

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

← →
antonn © (2009-11-21 01:07) [14]

зато можно использовать в качестве уникального значения поста :)

← →
Anatoly Podgoretsky © (2009-11-21 01:09) [15]

> так для каждого форума должна быть отдельная нумерация т.
> е. придется навернуть столько же столбцов сколько и форумов
> на сайте (16-20). А учитывая что таблица с данными окажется
> огромной то будет очень много лишней информации в таблице.
> Или нет?

Необязательно достаточно два поля - GroupID и ID (общее для всех групп) и эти два поля должны быть первичным индексом. Неудобно что придется получать максимальный для группы ИД + 1 и это сериализировать. Для сервера это выполнимо. Размер таблицы конечно будет большой.

← →
antonn © (2009-11-21 01:11) [16]

что значит "сериализировать" в мускле?

← →
Anatoly Podgoretsky © (2009-11-21 01:15) [17]

> antonn (21.11.2009 01:07:14) [14]

Только в комбинации с группой

← →
Anatoly Podgoretsky © (2009-11-21 12:51) [18]

> antonn (21.11.2009 01:11:16) [16]

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

← →
antonn © (2009-11-21 22:22) [19]

> Только в комбинации с группой

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

← →
Anatoly Podgoretsky © (2009-11-22 00:51) [20]

> antonn (21.11.2009 22:22:19) [19]

Это убивает NNTP

← →
antonn © (2009-11-22 00:55) [21]

а если учесть, что база может использоваться не только для ннтп? :)

← →
Anatoly Podgoretsky © (2009-11-22 17:45) [22]

> antonn (22.11.2009 00:55:21) [21]

Но мы то про конкретную систему говорим. NNTP подходит в любую сторону, а вот наоборот нет.

← →
xayam © (2009-11-24 00:43) [23]

> Anatoly Podgoretsky © (22.11.09 17:45) [22]
> > antonn (22.11.2009 00:55:21) [21]
> Но мы то про конкретную систему говорим. NNTP подходит в
> любую сторону, а вот наоборот нет.

можно еще посмотреть как выглядят данные из таблицы, скажем, XForum0 . Просто не понятно, например, чем отличаются поля TITLE и HEADER по содержанию (rfc пока не дочитал)

← →
Anatoly Podgoretsky © (2009-11-24 07:45) [24]

> xayam (24.11.2009 00:43:23) [23]

TITLE это тема письма, а HEADER это заголовки сообщения.

← →
xayam © (2009-11-24 14:47) [25]

> Anatoly Podgoretsky © (24.11.09 07:45) [24]
> HEADER это заголовки сообщения.

т.е. судя по rfc1036 заголовок может выглядеть примерно так
From: jerry@eagle.ATT.COM (Jerry Schwarz) Path: cbosgd!mhuxj!mhuxt!eagle!jerry Newsgroups: news.announce Subject: Usenet Etiquette -- Please Read Message-ID: <642@eagle.ATT.COM> Date: Fri, 19 Nov 82 16:14:55 GMT Followup-To: news.misc Expires: Sat, 1 Jan 83 00:00:00 -0500 Organization: AT&T Bell Laboratories, Murray Hill
т.е. содержит в одном столбце все заголовки сообщения, в том числе Message-ID. Правильно?

← →
xayam © (2009-11-24 14:59) [26]

Если так то я не пойму зачем дублировать информацию. Subject (TITLE) - хранится в отдельном столбце, Date (REPLYDATE) - тоже, From (EMAIL + LOGIN) - тоже в отдельном столбце. Вообще и Message-ID можно сохранить отдельно и т.д. А когда заголовки в отдельных столбцах, то их можно проиндексировать и организовать по ним поиск.

← →
Anatoly Podgoretsky © (2009-11-24 15:04) [27]

> xayam (24.11.2009 14:47:25) [25]

Ну приблизительно так, только к rfc это не имеет прямого отношения,
Там следующее

From: =?Utf-8?B?dG9ta2F0?= <tomkat_s@inbox.ru> Subject: =?Utf-8?B?0J/RgNC+0LHQu9C10LzQsCDRgSBBRE8g0LIgRmFzdFJlcG9ydA==?= Newsgroups: Databases Date: Fri, 06 Oct 2006 15:45:00 Lines: 1 Message-ID: <1160135159.0@delphimaster.ru> Content-Type: text/html; charset="Windows-1251" Content-Transfer-Encoding: 8bit

Это скомбинированый заголовок из информации от ДМ и предназначен он для того, что бы не генерировать его при каждом ответе на команду ARTICLE и подобные. Видно, что два последних заголовка не относятся к 1036
Тело (body) хранится отдельно, в поле body

и передаче клиента используются все необходимые поля из БД и вроде еще часть добавляется на лету (уже точно не помню). Не используется PATH:, FOLLOW-UP:, EXPIRES:, ORGANISATION: то есть в соответствии с последними тенденциями, заголовков должен быть минимум, RFC это разрешает, даже для обязательных заголовков.

← →
Anatoly Podgoretsky © (2009-11-24 15:08) [28]

Это из темы
tomCat tomkat@inbox.ru 06.10.2006 15:45

<H4>tomkat (<FONT COLOR="#008000">06.10.2006 15:45</FONT>) <FONT COLOR="#808080">[D6]</FONT></H4><p>Hi, мастера! Проблема в следующем : <br>есть запрос <br>select<br>count(a.accountno) kol<br>from tAccount a<br>where <br>a.accounttype in (6,7,8) <br>and a.createdate>="01.07.2006"<br>в SQLNavigator все прекрасно работает , возвращает поле с количеством записей , <br>как только я пишу этот запрос в компоненте TfrADOQuery FastReporta<br>он возвращает количество 0 <br>Убираю строку and a.createdate>="01.07.2006" - все работает <br>Что это ? глюк ? чей ? <br>Заранее благодарен</p>

← →
Anatoly Podgoretsky © (2009-11-24 15:18) [29]

> Если так то я не пойму зачем дублировать информацию

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

Принимай как есть, как должное. Внутреннее дело системы.

← →
xayam © (2009-11-28 03:19) [30]

ну ладно структуру я переделал, вот теперь запрос примерно так выглядет на запрос "del*" автор "Skywalker" форум 1:
select * from `messages` where (`LOGIN` = "Skywalker") and (`GROUP` = "1") and (match(`TITLE` , `LOGIN`, `BODY`) against("del*" IN BOOLEAN MODE)) group by `ID_MSG`,`ID_GROUP`,`ID_NNTP`,`ID_DM`,`REPLYNR`,`ID_DM_AUTHOR`,`LOGIN`,`EMAIL`,` REPLYDATE`, `TITLE`,`CNT`,`ATTRIBUTES`,`HEADER`,`BODY`,`ARCHIVE` having `REPLYNR` = 0 order by `REPLYDATE` desc limit 0, 100
Проблема в том, что я еще хотел показать и подсветить цветом только те сообщения, которые содержат поисковую строку S. Можно ли с помощью sql добавить еще один столбец, который будет содержать список значений через запятую из столбца ID_MSG, которые будут ссылаться на нужные сообщения? Или все равно придется делать для каждой ветки запрос, вручную разбирать строку S и подсвечивать? Не хотелось к базе лишний раз обращаться.

← →
antonn © (2009-11-28 14:08) [31]

> Проблема в том, что я еще хотел показать и подсветить цветом
> только те сообщения, которые содержат поисковую строку S.
>

я при выводе подсвечиваю

← →
xayam © (2009-11-28 14:46) [32]

> antonn © (28.11.09 14:08) [31]
> я при выводе подсвечиваю

а средствами sql никак не решить хотя бы часть проблемы, чтобы при отображении ветки не делать снова запрос на полнотекстовый поиск и строку S не передавать (хотя можно передать через $_SESSION[])?

Но тут оказалось есть еще проблема. Например такой запрос:
select * from `dms_messages` where (match(`TITLE` , `LOGIN`, `BODY`) against("Sleepyhead" IN BOOLEAN MODE)) and `REPLYNR` >= 0 limit 0, 30
выдает такой ответ http://xayam.com/query1.jpg
Но проблема в том что нужно отобразить только сообщения автора ветки (т.е. REPLYNR = 0), если я добавлю условие REPLYNR = 0 то отфильтруются все остальные авторы сообщений, а нужно найти для них в базе REPLYNR = 0, даже если автор сообщения (LOGIN) другой. Это реально вообще? У меня что-то не получается.

или вообще переделать логику запроса лучше
select * from `dms_messages` where (match(`TITLE` , `LOGIN`, `BODY`) against("Sleepyhead" IN BOOLEAN MODE)) group by `ID_DM` limit 0, 30
Тогда отобразится только по одному сообщению на ветку, так как раз и надо. Кажется разобрался. С group by раньше не работал, а очень удобно :)

еще узнал такую особенность полнотекстового поиска в режиме BOOLEAN MODE - не нужно делать отдельный полнотекстовый индекс по всем столбцам (`TITLE` , `LOGIN`, `BODY`), достаточно два раздельных (`TITLE`) и (`LOGIN`, `BODY`). За счет чего получилось значительно уменьшить размер базы. Теперь база с данными и индексами занимает даже меньше места, чем все разархивированные xml-файлы :)

> xayam © (28.11.09 14:46) [32]
> > antonn © (28.11.09 14:08) [31]
> > я при выводе подсвечиваю
> а средствами sql никак не решить хотя бы часть проблемы,
> чтобы при отображении ветки не делать снова запрос на полнотекстовый
> поиск и строку S не передавать (хотя можно передать через
> $_SESSION[])

вообще $_SESSION[] скорей всего не подойдет для передачи строки запроса S, так как у пользователя может быть открыто несколько окон браузера и он может делать в них разные запросы, а $_SESSION[] одинаков для всех окон. Как еще можно передать? Или я не прав?

> antonn

хотя можно наверное забить на это. Кто будет больше одного окна открывать для поиска? Как думаешь?

я в get передаю. Как минимум это нужно в том случае, если передавать ссылку другим (или сохранить в избранном). Вообще все "запросы" я передаю в GET (фильтрация, поиск), поиск тоже запрошенная информация :)

> antonn © (28.11.09 17:19) [37]
>
> я в get передаю. Как минимум это нужно в том случае, если
> передавать ссылку другим (или сохранить в избранном). Вообще
> все "запросы" я передаю в GET (фильтрация, поиск), поиск
> тоже запрошенная информация :)

я сделал ссылку для просмотра ветки вида /view/1-54321/0-10,30,42-44/ т.е отбирается ветка из форума 1 с ID=54321 и только ответы от 0-ого до 10-ого, 30-й и от 42-ого до 44-ого. Можно ссылку вплоть до конкретного сообщения давать, только подсветки не будет, подсветка только для тех кто ищет и соответственно SESSION заполняется. По-моему нормально должно быть.

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

> antonn © (28.11.09 23:22) [39]
> сессии - это костыли, они могут не работать, а значит будут
> теряться результаты поиска.

вообще теряться не должно, если настроить хранить id сессии в форме.
Даже если будет теряться только подсветка (сейчас нету ее и нормально), а ветка в любом случае отобразиться.

я про клиента говорю, сессия прикрепляется к клиенту когда тот передает ее SID. А хранится он в куках или в урлах. Если куки отключены, а урлы у тебя "ЧПУшатся" - где будет сид? :)

> antonn © (29.11.09 00:22) [41]
> я про клиента говорю, сессия прикрепляется к клиенту когда
> тот передает ее SID. А хранится он в куках или в урлах.
> Если куки отключены, а урлы у тебя "ЧПУшатся" - где будет
> сид? :)

можно вручную в форму вставить, как вот здесь описано http://php.spb.ru/php/session.html
Если вы хотите поддерживать работу сайта с отключенными куками но не хотите, чтобы ПХП автоматически заменял ссылки и формы, либо если ПХП на какой-то конкретной ссылке/форме глючит и не вставляет идентификатор, проделывайте это вручную. Это очень просто. Для начала отпределим новую константу SIDFORM (после session_start()): define ("SIDFORM","<input type=hidden name=".session_name()." value=".session_id().">"); Представим, что у нас есть ссылки и формы (еще бывают фреймы и теги типа AREA, но там делают аналогично). С помощью двух констант SID - определена в ПХП и SIDFORM - придумали сами себе, переработаем текст: ------------------БЫЛО (ПХП-файл)------------------ <html><body> Ссылка: <a href=test.php?a=1>click here</a> ... Форма: <form action=test.php> .... </form> ------------------МЫ ЗАМЕНЯЕМ НА (новая версия ПХП-файла)------------------ <html><body> Ссылка: <a href=test.php?a=1&<?=SID?>>click here</a> .... Форма: <form action=test.php> <?=SIDFORM?> .... </form> ------------------РЕЗУЛЬТАТ ВЫПОЛНЕНИЯ (это получит браузер)------------------ <html><body> Ссылка: <a href=test.php?a=1&PHPSESSID=ac4f4a45bdc893434c95dcaffb1c1811>click here</a> .... Форма: <form action=test.php> <input type="text" name="PHPSESSID" value="ac4f4a45bdc893434c95dcaffb1c1811"> .... </form>

а ну да че то я торможу, в урл же нельзя там же get :( мда фигово

> antonn

Еще проблема. Узнал что в php есть функция
tidy_repair_string — Repair a string using an optionally provided configuration file Description string tidy_repair_string ( string $data [, mixed $config [, string $encoding ]] )
не знаешь случайно какие параметры нужно передавать в $config , если обрабатывается xml-строка?

первый раз про нее слышу :))

> antonn © (01.12.09 20:58) [45]
> первый раз про нее слышу :))

ну ладно, я как xhtml сделал и вырезал начальные-конечные теги - так что работает. Ошибок в старых архивах целая куча, а эта функция исправляет автоматически, очень удобно

Запрос к MySQL Найти похожие ветки