> в 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

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

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

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

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

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

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

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