Так как переехали на новый хостинг к 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