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

то есть теперь если написать автора в поле "Автор сообщения" то
найдет все ветки где автор писал в лбом качестве что спрашивающего что отвечающего
но в столбце результата "Автор сообщения" будет указан этот же автор

даже такой запрос где много выходных строк
http://delphimaster.net/?s=delp*
относительно быстро обрабатывает

может кто подскажет как еще оптимизировать такой запрос?

select SQL_CALC_FOUND_ROWS r.*, b.LOGIN AS LOGIN0 FROM (
select *
from (
select a.* from dms_messages as a
 where (1) and (match(a.TITLE) against('газ* del* colo*' IN BOOLEAN MODE))
UNION
select a.* from dms_messages as a
 where (1) and (match(a.LOGIN) against('газ* del* colo*' IN BOOLEAN MODE))
UNION
select a.* from dms_messages as a
 where (1) and (match(a.BODY) against('газ* del* colo*' IN BOOLEAN MODE))
) t
group by t.ID_DM
order by t.REPLYDATE desc
limit 0, 50
) r, dms_messages as b
 WHERE (b.ID_DM=r.ID_DM and b.REPLYNR=0)

время выполнения на полной базе сервера - примерно 12-13 секунд,
что достаточно много :)
хотелось бы сократить время выполнения в 2-2.5 раза
EXPLAIN EXTENDED QUERY - http://delphimaster.net/gets/expdata.pdf

хотя лимит надо вынести наверх, join дает такое же время

select SQL_CALC_FOUND_ROWS r.*, b.LOGIN AS LOGIN0 FROM (
select *
from (
 select a.* from dms_messages as a
 where (1) and (match(a.TITLE) against('газ* del* colo*' IN BOOLEAN MODE))
UNION
 select a.* from dms_messages as a
 where (1) and (match(a.LOGIN) against('газ* del* colo*' IN BOOLEAN MODE))
UNION
 select a.* from dms_messages as a
 where (1) and (match(a.BODY) against('газ* del* colo*' IN BOOLEAN MODE))
) t
group by t.ID_DM
order by t.REPLYDATE desc
) r
LEFT JOIN dms_messages as b ON b.ID_DM=r.ID_DM
WHERE  b.REPLYNR=0
limit 0, 50


так не будет быстрее, если заменить  
select *
from (
 select a.* from dms_messages as a
 where (1) and (match(a.TITLE) against('газ* del* colo*' IN BOOLEAN MODE))
UNION
 select a.* from dms_messages as a
 where (1) and (match(a.LOGIN) against('газ* del* colo*' IN BOOLEAN MODE))
UNION
 select a.* from dms_messages as a
 where (1) and (match(a.BODY) against('газ* del* colo*' IN BOOLEAN MODE))
) t
group by t.ID_DM
order by t.REPLYDATE desc
на
select a.* from dms_messages as a
where
 ((1) and (match(a.TITLE) against('газ* del* colo*' IN BOOLEAN MODE))) or
 ((1) and (match(a.LOGIN) against('газ* del* colo*' IN BOOLEAN MODE))) or
 ((1) and (match(a.BODY) against('газ* del* colo*' IN BOOLEAN MODE))
group by a.ID_DM
order by a.REPLYDATE desc

по сути тоже самое, но за 1 проход, вместо 3х с UNION + запрос из под запроса вряд ли использует индексы если они есть в таблице для группировки

> по сути тоже самое, но за 1 проход, вместо 3х с UNION +
> запрос из под запроса вряд ли использует индексы если они
> есть в таблице для группировки

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

> [7] xayam ©   (16.03.19 22:51)

Базу же ты проектировал? Чёт мне кажется, что надо структуру как-то моменять и/или индексы. Не должно же так тормозить.

>  Не должно же так тормозить.

2.8 млн сообщений вообще-то, может и должно так быть,
либо с нуля все переделывать

без использования * выдает все равно как если бы использовал его
а так - норм

> ВладОшин ©   (17.03.19 09:56) [10]
> без использования * выдает все равно как если бы использовал
> его
> а так - норм

если серьезно, то ничего нормального,
когда столько времени тратится на простой запрос.
Можно попробовать ускориться с помощью такого сервиса -
Google Cloud BiqQuery
они поддерживают таблицы и sql-подобный синтаксис запросов
тогда будет быстро выполняться, но это платить еще за сервер,
я вообще подумаю, если получиться то сделаю может быть

> Google Cloud BiqQuery

к сожалению у них нет full-text search, только регулярные выражения и контейнс
и вообще квоты там еще даже чтобы загрузить все архивы не хватает
(только 100 мб загрузилось, это видимо квота на 1 день)

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


> Google Cloud BiqQuery

хм.. я думал у тебя все самописное..
БД - какая?

а.. мускул, судя по запросам.. пардон )

>xayam ©   (16.03.19 17:58) [5]
можно увидеть DDL dm_messages?
вложенный запрос должен возвращать запись с максимальной REPLYDATE по каждому ID_DM?

> можно увидеть DDL dm_messages?


CREATE TABLE dms_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 COMMENT,
 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 DEFAULT NULL,
 ARCHIVE varchar(10) DEFAULT NULL,
 PRIMARY KEY (ID_MSG)
)
ENGINE = MYISAM,
AUTO_INCREMENT = 2815755,
AVG_ROW_LENGTH = 553,
CHARACTER SET utf8,
CHECKSUM = 0,
COLLATE utf8_general_ci,
ROW_FORMAT = fixed;


> вложенный запрос должен возвращать запись с максимальной
> REPLYDATE по каждому ID_DM?

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

>упорядочивает по дате или по-другому в зависимости от пользов.данных
пусть у вас есть две записи в dms_messages:
R1 (ID_DM, REPLYDATE) = (1, "2019-03-20")
R2 (ID_DM, REPLYDATE) = (2, "2019-03-21")

какую из них хотите видеть в результате запроса:

select *
from (
select a.* from dms_messages as a
) t
group by t.ID_DM
order by t.REPLYDATE desc

?

>niteshade ©   (21.03.19 08:00) [17]
опечатка
следует читать как:
пусть у вас есть две записи в dms_messages:
R1 (ID_DM, REPLYDATE) = (1, "2019-03-20")
R2 (ID_DM, REPLYDATE) = (1, "2019-03-21")

> какую из них хотите видеть в результате запроса:
> пусть у вас есть две записи в dms_messages:
> R1 (ID_DM, REPLYDATE) = (1, "2019-03-20")
> R2 (ID_DM, REPLYDATE) = (1, "2019-03-21")

вообще такого не должно быть
ID_GROUP, ID_DM уникальный, но
да по ходу тут ошибка - в group by должно быть еще указано ID_GROUP

> в group by должно быть еще указано ID_GROUP

правда практически это ни на что не влияет, ID_DM уникальный для каждой даты

> ID_DM уникальный для каждой даты

точнее replydate - это дата+время поста

как минимум, напрашивается полнотекстовый индекс на (title, login, body)
это возможно?
и использование match(a.title, a.login, a.body) against('газ* del* colo*' IN BOOLEAN MODE)
без union

ID_DM, ID_GROUP - что это? своими словами


select *
from (
select a.* from dms_messages as a
) t
group by t.ID_DM
order by t.REPLYDATE desc

что именно пытаетесь получить этим запросом?

> niteshade ©   (21.03.19 09:11) [22]
> как минимум, напрашивается полнотекстовый индекс на (title,
>  login, body)
> это возможно?
> и использование match(a.title, a.login, a.body) against('газ*
> del* colo*' IN BOOLEAN MODE)
> без union

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

> ID_DM, ID_GROUP - что это? своими словами

вот наша ветка
http://pda.delphimaster.net/?id=1552713376&n=3
для нее ID_DM=1552713376 и ID_GROUP=3

> что именно пытаетесь получить этим запросом?

так как вся ветка хранится в одной таблице, то ID_DM дублируется для
каждого сообщения, поэтому нужен group by чтобы получить только одну
строку в результате
(причем не важно какую - остальные столбцы дублируют нужную информацию)

> остальные столбцы дублируют нужную информацию

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

> (причем не важно какую - остальные столбцы дублируют нужную
> информацию)

хотя реально выбирается сообщение ветки где найден поисковый запрос
и этого достаточно

>xayam ©   (21.03.19 09:52) [23]
>он сказал так медленнее будет работать,
три сканирования индекса будут работать быстрее одного?

вероятно, у нас возникло недопонимание

вариант для трёх индексов + union

mysql> select count(1)
   -> from(
   ->     select a.* from test as a
   ->       where match(a.title) against('som* tex* text*' IN BOOLEAN MODE)
   ->     UNION
   ->     select a.* from test as a
   ->       where match(a.login) against('som* tex* text*' IN BOOLEAN MODE)
   ->     UNION
   ->     select a.* from test as a
   ->       where match(a.body) against('som* tex* text*' IN BOOLEAN MODE)
   -> ) t;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (25.80 sec)


вариант для одного:

mysql> select count(1)
   -> from test as a
   -> where match(a.login, a.title, a.body) against('som* tex* text*' IN BOOLEAN MODE);
+----------+
| count(1) |
+----------+
|   316928 |
+----------+
1 row in set (1.03 sec)


> три сканирования индекса будут работать быстрее одного?
возможно mysql его тут не использует, делая полный скан (оптимизатор/план кривой?)... в предложенном случае по всей таблице, а в его варианте по результату под запроса...
в общем выборки с union медленнее но на фоне группировки по всей таблице вместо результата этого не видно...

проверь, вместо
select a.* from dms_messages as a
where
 ((1) and (match(a.TITLE) against('газ* del* colo*' IN BOOLEAN MODE))) or
 ((1) and (match(a.LOGIN) against('газ* del* colo*' IN BOOLEAN MODE))) or
 ((1) and (match(a.BODY) against('газ* del* colo*' IN BOOLEAN MODE))
group by a.ID_DM
order by a.REPLYDATE desc
так
select *
from (
 select * from dms_messages
 where
   ((1) and (match(TITLE) against('газ* del* colo*' IN BOOLEAN MODE))) or
   ((1) and (match(LOGIN) against('газ* del* colo*' IN BOOLEAN MODE))) or
   ((1) and (match(BODY) against('газ* del* colo*' IN BOOLEAN MODE))
) a
group by a.ID_DM
order by a.REPLYDATE desc

> вариант для одного:
> ?
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> mysql> select count(1)
>     -> from test as a
>     -> where match(a.login, a.title, a.body) against('som*
> tex* text*' IN BOOLEAN MODE);
> +----------+
> | count(1) |
> +----------+
> |   316928 |
> +----------+
> 1 row in set (1.03 sec)

что-то я не понял что это за число?

>что-то я не понял что это за число?
кол-во выбранных записей

>sniknik ©   (21.03.19 12:00) [27]
>возможно mysql его тут не использует
судя по плану запроса от автора, использует

>проверь, вместо
это будет явно быстрее варианта автора, но зачем, если по индексу (login, title, body) всё равно будет быстрее?

> если по индексу (login, title, body) всё равно будет быстрее?

я проверял получается медленнее

>xayam ©   (21.03.19 12:45) [31]

в "niteshade ©   (21.03.19 11:47) [26]" закралась ошибка
правильные тайминги выглядят так:

ваш текущий вариант

mysql> select count(1)
   -> from(
   ->     select a.* from test as a
   ->       where match(a.title) against('som* tex* text*' IN BOOLEAN MODE)
   ->     UNION
   ->     select a.* from test as a
   ->       where match(a.login) against('som* tex* text*' IN BOOLEAN MODE)
   ->     UNION
   ->     select a.* from test as a
   ->       where match(a.body) against('som* tex* text*' IN BOOLEAN MODE)
   -> ) t;
+----------+
| count(1) |
+----------+
|   316928 |
+----------+
1 row in set (32.19 sec)


вариант с композитным индексом (login, title, body)

mysql> select count(1)
   -> from test as a
   -> where match(a.login, a.title, a.body) against('som* tex* text*' IN BOOLEAN MODE);
+----------+
| count(1) |
+----------+
|   316928 |
+----------+
1 row in set (2.95 sec)


возможно, в ваших экспериментах полнотекстовый индекс не был задействован

>sniknik ©   (21.03.19 12:00) [27]
>в общем выборки с union медленнее но на фоне группировки по всей таблице вместо результата >этого не видно...
сложность группировки - O(n)
union  - O(n log n) (минимум), для каждого набора, а их три в нашем случае

> вариант с композитным индексом (login, title, body)

ты на каких данных проверяешь?
я то на реальных проверял

>xayam ©   (21.03.19 13:03) [33]
>ты на каких данных проверяешь?
это не имеет значения в данном случае
ОДНО сканирование индекса будет НЕ медленнее ТРЁХ сканирований, да ещё и отягощённых сортировкой ТРЁХ наборов данных, вызванной union

можно увидеть тайминги обоих вариантов с вашей системы?

впрочем, не навязываюсь

> ОДНО сканирование индекса будет НЕ медленнее ТРЁХ сканирований
+ 1

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

> можно увидеть тайминги обоих вариантов с вашей системы?
> впрочем, не навязываюсь

вообще сейчас уже нет смысла - я решил если получиться на сфинкс переехать
там по любому будет быстрее - по крайне мере обещают меньше секунды

если уж и юзать юнион, то юзать надо юнион олл

чтобы не делался ненужный дистинкт

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

я ап том, что юнион всегда будет делать (сам) дистинкт. даже если там все уникальное.
юнион олл - не будет

что-то этот сфинкс с ssl не идеально работает