Вопрос по SQL

← →
xayam © (2009-12-09 17:34) [0]

Как объединить два запроса?
select * from `messages` where (match(`TITLE` , `LOGIN`, `BODY`) against(""Игорь Шевченко"" IN BOOLEAN MODE)) group by `ID_DM` order by `LOGIN` asc
и
select `LOGIN` from `messages` where `ID_DM` = "--из первого запроса--" and `REPLYNR` = "0"
Структура таблицы такая:
CREATE TABLE `messages` ( `ID_MSG` bigint(20) unsigned NOT NULL auto_increment, `ID_GROUP` int(11) unsigned NOT NULL, `ID_NNTP` int(11) unsigned NOT NULL, `ID_DM` int(12) unsigned NOT NULL `REPLYNR` int(10) unsigned NOT NULL, `ID_DM_AUTHOR` int(12) unsigned default NULL, `LOGIN` varchar(100) default NULL, `EMAIL` varchar(50) default NULL, `REPLYDATE` varchar(16) default NULL, `TITLE` varchar(255) default NULL, `CNT` int(11) unsigned NOT NULL default "0", `ATTRIBUTES` varchar(100) default NULL, `BODY` text, `ARCHIVE` varchar(10) default NULL, PRIMARY KEY (`ID_MSG`), UNIQUE KEY `ID_GROUPID_NNTP` (`ID_GROUP`,`ID_NNTP`), ... FULLTEXT KEY `TITLE` (`TITLE`), FULLTEXT KEY `BODY` (`LOGIN`,`BODY`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
REPLYNR = 0 - соответствует автору ветки, в конечном результате нужно отобразить только авторов веток, которые нашлись в первом запросе, но поскольку ветки хранятся в виде отдельных сообщений, то и находятся только конкретные сообщения с любым REPLYNR, а нужно именно = 0. Конечно можно сделать сначала первый запрос, и для каждой строки результата сделать второй и поле `LOGIN` будет соответствовать автору ветки, но во-первых, это достаточно медленно, во-вторых, нельзя будет упорядочить результат по автору.

← →
tesseract © (2009-12-09 18:14) [1]

LEFT JOIN ту же таблицу под другим именем.

← →
xayam © (2009-12-09 19:07) [2]

> tesseract © (09.12.09 18:14) [1]
> LEFT JOIN ту же таблицу под другим именем.

так?
select * from messages as a left join messages as b ON (a.ID_DM=b.ID_DM and b.REPLYNR=0) where (match(a.TITLE , a.LOGIN, a.BODY) against(""Игорь Шевченко"" IN BOOLEAN MODE)) group by a.ID_DM order by b.LOGIN asc limit 0, 100
но мне нужно присоединить только один столбец b.LOGIN, а не всю таблицу. Это возможно? И вообще у меня это запрос на 800.000 строк в таблице выполнялся 50 секунд, многовато что-то. Можно ли оптимизировать?

← →
Anatoly Podgoretsky © (2009-12-09 19:16) [3]

> xayam (09.12.2009 17:34:00) [0]

Думаю так

select LOGIN from `messages`
where (match(`TITLE` , `LOGIN`, `BODY`) against(""Игорь Шевченко"" IN BOOLEAN MODE) and `REPLYNR` = "0")
order by `LOGIN` asc

Группирование не требуется.

← →
xayam © (2009-12-09 19:34) [4]

> Anatoly Podgoretsky © (09.12.09 19:16) [3]
> select LOGIN from `messages`
> where (match(`TITLE` , `LOGIN`, `BODY`) against(""Игорь
> Шевченко"" IN BOOLEAN MODE) and `REPLYNR` = "0")
> order by `LOGIN` asc

вот так точно неправильно, так будет искать строку "Игорь Шевченко" только в сообщении автора, а нужно по всей ветке

> Группирование не требуется

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

← →
kaif (2009-12-09 19:35) [5]

Разве можно делать SQL-запросы against "Игорь Шевченко"?
Он же модератор!

← →
test © (2009-12-09 19:39) [6]

xayam © (09.12.09 19:07) [2]
Перечисли поля которые тебе нужны, например

select a.*, b.id, b.name from table1 a
left join table2 b on a.id = b.aid

← →
xayam © (2009-12-09 19:40) [7]

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

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

← →
Anatoly Podgoretsky © (2009-12-09 20:07) [8]

> xayam (09.12.2009 19:34:04) [4]

Не по всей ветке, а только по вопросу REPLYNR=0

← →
Anatoly Podgoretsky © (2009-12-09 20:10) [9]

> xayam (09.12.2009 19:34:04) [4]

Комбинация id_dm + replynr=0 уникальная, строго одна запись, поэтому группирование по id_dm бессмысленно.

← →
xayam © (2009-12-09 20:18) [10]

> Anatoly Podgoretsky © (09.12.09 20:07) [8]
> Не по всей ветке, а только по вопросу REPLYNR=0

это если бы я искал "Игорь Шевченко" автора ветки тогда так LOGIN="Игорь Шевченко" and REPLYNR=0, но я ищу по всему тексту т.е. match(`TITLE` , `LOGIN`, `BODY`) against(""Игорь Шевченко"" IN BOOLEAN MODE)
вот получился такой запрос
select a.*, b.LOGIN from messages as a left join messages as b ON (a.ID_DM=b.ID_DM and b.REPLYNR=0) where (match(a.TITLE , a.LOGIN, a.BODY) against(""Игорь Шевченко"" IN BOOLEAN MODE)) group by a.ID_DM order by b.LOGIN asc
работает вроде как надо, но тормоза страшные 50-55 секунд выполняется :(

← →
Игорь Шевченко © (2009-12-09 20:27) [11]

нефиг меня объединять

← →
test © (2009-12-09 20:36) [12]

Попробуй индексы по полям a.ID_DM, b.ID_DM в БД зделать чтоб быстрее искала ИМХО

← →
xayam © (2009-12-09 20:37) [13]

> test © (09.12.09 20:36) [12]
> Попробуй индексы по полям a.ID_DM, b.ID_DM в БД зделать
> чтоб быстрее искала ИМХО

так это с индексами так работает

← →
xayam © (2009-12-09 20:38) [14]

> Игорь Шевченко © (09.12.09 20:27) [11]
> нефиг меня объединять

:) ты просто давно живешь на форуме, а у меня в базе только старые архивы, вот на тебе экспериментирую

← →
test © (2009-12-09 20:46) [15]

xayam © (09.12.09 20:37) [13]
План запроса построй посмотри кто больше всех времени занимает, только так имхо.

← →
Anatoly Podgoretsky © (2009-12-09 20:46) [16]

> xayam © (09.12.09 20:18) [10]

Придется привести цитату из тебя же.

> select `LOGIN` from `messages`
> where `ID_DM` = "--из первого запроса--" and `REPLYNR` = "0"

← →
xayam © (2009-12-09 20:52) [17]

> Anatoly Podgoretsky © (09.12.09 20:46) [16]
> > select `LOGIN` from `messages`
> > where `ID_DM` = "--из первого запроса--" and `REPLYNR` = "0"

я там еще дописал, что

> Конечно можно сделать сначала первый запрос, и для каждой
> строки результата сделать второй и поле `LOGIN` будет соответствовать
> автору ветки

т.е. здесь во втором запросе находится только LOGIN автора ветки для каждой строки результата первого запроса.
Но я запускал и Ваш запрос он работает неправильно, выдает много дублирующих строк.

← →
Anatoly Podgoretsky © (2009-12-09 20:55) [18]

> xayam (09.12.2009 20:52:17) [17]

и номер 0
То есть уникальная пара, поэтому группирование лишнее.

← →
xayam © (2009-12-09 21:09) [19]

> test © (09.12.09 20:46) [15]
> План запроса построй посмотри кто больше всех времени занимает,
> только так имхо.

вот такой план http://xayam.com/plan.jpg

← →
tesseract © (2009-12-09 21:10) [20]

> работает вроде как надо, но тормоза страшные 50-55 секунд
> выполняется :(

А чего ты хочешь? Если у тебя against с ТЕКСТОМ, да ещё по трём полям ? Полнотекстовой индекс если только заводить. В составной индекс сразу три поля да у тебя попадать не должны по любому. Делать или view или как-то с виртуальной таблицей. Можно ещё поиграться с хитрозадой собсвенной хэш-функцией - но это уже DDL, а ты этого не указывал :-)

← →
xayam © (2009-12-09 21:20) [21]

> xayam © (09.12.09 21:09) [19]
> вот такой план http://xayam.com/plan.jpg

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

← →
tesseract © (2009-12-09 21:31) [22]

> режиме BOOLEAN MODE можно иметь два индекса по составляющим
> полям

В виртуальной таблице может и выйдет.

> можно иметь два индекса по составляющим полям

Может имелся ввиду СОСТАВНОЙ индекс ?

← →
xayam © (2009-12-09 21:40) [23]

> tesseract © (09.12.09 21:31) [22]
> > можно иметь два индекса по составляющим полям
> Может имелся ввиду СОСТАВНОЙ индекс ?

может быть, я уже не помню, где это прочитал.

← →
xayam © (2009-12-09 21:49) [24]

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

← →
tesseract © (2009-12-09 21:57) [25]

> но хотелось бы быстрее,

Доп поле + триггер на заполнение при записи. Других вариантов моя пропитанная Dbase и MsSql голова придумать не может.

← →
test © (2009-12-09 21:58) [26]

Может как то подзапросом попробовать?

Select a.*, b.name from (select * from table1 where name like "%е-мое имя%") a
left join table2 on a.id = b.aid

← →
test © (2009-12-09 22:01) [27]

Или вообще вот так

select a.*, (select name from table2 b where b.aid = a.id) as name from
table1 where name like "%Суприм%"

← →
xayam © (2009-12-09 22:13) [28]

> test
> Select a.*, b.name from (select * from table1 where name
> like "%е-мое имя%") a
> left join table2 on a.id = b.aid
>
> Или вообще вот так
>
> select a.*, (select name from table2 b where b.aid = a.id)
> as name from
> table1 where name like "%Суприм%"

это ты че то замутил сильно :)

← →
xayam © (2009-12-09 22:20) [29]

> tesseract © (09.12.09 21:57) [25]
> Доп поле + триггер на заполнение при записи. Других вариантов
> моя пропитанная Dbase и MsSql голова придумать не может.

а что в это поле записывать, чтобы выборка была быстрее?

← →
xayam © (2009-12-09 22:38) [30]

переставил столбец b.LOGIN вначало - 13 секунд выполняется :) еще бы раз в два сократить

← →
tesseract © (2009-12-09 22:54) [31]

> а что в это поле записывать, чтобы выборка была быстрее?

"Игорь Шевченко" = boolean :-) И в составной индекс его.

← →
test © (2009-12-10 01:13) [32]

xayam © (09.12.09 22:13) [28]
Прогони вариант из [27] должно помочь, скорость в таком варианте хорошая.

> test © (10.12.09 01:13) [32]
> Прогони вариант из [27] должно помочь, скорость в таком
> варианте хорошая.

да тут like не поможет, у меня же полнотекстовый поиск. А подзапрос я уже пробовал, такое же время получается примерно

в принципе 13-15 секунд это еще нормально, но это на самый сложный запрос тут можно и подождать, на простые запросы, где есть точный логин автора ветки гораздо быстрее порядка 1-2 секунд.

xayam © (10.12.09 01:15) [33]
Там особенность не в like, а в том что ты в результирующий набор добавляешь значения из другой таблицы. Экономия.

Надо срочно потрепаться в этой теме, а то из прочего в БД перенесут.

> test © (10.12.09 01:49) [35]
> Там особенность не в like, а в том что ты в результирующий
> набор добавляешь значения из другой таблицы. Экономия.

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

> Надо срочно потрепаться в этой теме, а то из прочего в БД
> перенесут.

--здесь был оффтопик-- :)

xayam © (10.12.09 02:02) [37]
После выполнения select у тебя уже есть какой то набор данных, к этому набору ты налету добавляешь поле, вторая таблица основному запросу не мешает искать по тексту.

select a.*,(select `LOGIN` from `messages`
where `ID_DM` = a.id and `REPLYNR` = "0") as "login"
from `messages` as a
where (match(`TITLE` , `LOGIN`, `BODY`) against(""Игорь Шевченко"" IN BOOLEAN MODE))
group by `ID_DM`
order by `LOGIN` asc

Вот примерно так я имел в виду, таблицы соединены, значение из `messages` есть, в основном запросе таблица `messages` не участвует, к таблице `messages` обращаются только когда все значения уже есть и надо подставить значение login

#ifdef __OFFTOP_
/*чтобы не перенесли*/

И можно сколько угодно связывать кого угодно с кем угодно.

#endif //__OFFTOP_

select a.*, (select LOGIN from messages as b where b.ID_DM = a.ID_DM and a.REPLYNR = "0") as LOGIN2 from messages as a where (match(a.TITLE , a.LOGIN, a.BODY) against(""Игорь Шевченко"" IN BOOLEAN MODE)) group by a.ID_DM order by LOGIN2 asc
так?

точнее вот так надо
select a.*, (select LOGIN from messages as b where b.ID_DM = a.ID_DM and b.REPLYNR = "0" ) as LOGIN2 from messages as a where (match(a.TITLE , a.LOGIN, a.BODY) against(""Игорь Шевченко"" IN BOOLEAN MODE)) group by a.ID_DM order by LOGIN2 asc
выполняется 26.7 секунды

точнее нужно указать limit
select a.*, (select LOGIN from messages as b where b.ID_DM = a.ID_DM and b.REPLYNR = "0" ) as LOGIN2 from messages as a where (match(a.TITLE , a.LOGIN, a.BODY) against(""Игорь Шевченко"" IN BOOLEAN MODE)) group by a.ID_DM order by LOGIN2 asc limit 0, 30
поскольку без лимита у меня клиент вручную обрезает результат и поэтому долго получается

13.7 секунды, на 0.7 секунды дольше выполняется

еще интересно, mysql кеширует результаты запроса? А то заметил такую вещь, первый запрос выполняется 13 секунда, а последующие при переходе между страницами 1-3 секунды.

ладно кажись лучше не сделаю, еще внес несколько исправления для совместимости с сегодняшним отображением поиска, осталось просмотр нормальный постраничный сделать. Еще client.pl ваять для доступа dmclient"а по новому протоколу. Проблем целая куча короче :)

> test © (10.12.09 20:04) [45]
> Если не секрет, что пишешь?

да тот же http://www.delphimaster.net/ переделываю
примерно как этот сайт будет выглядеть:
http://xayam.com/view1.jpg
http://xayam.com/view2.jpg

Вопрос по SQL Найти похожие ветки