Время ожидания сервера Mysql для определенных запросов поиска

Попробуйте реорганизовать свой внутренний запрос следующим образом.

     SELECT a.projectid 
       FROM (
              SELECT DISTINCT projectid
                FROM projectsearch
               WHERE searchtext LIKE '%am%'
            ) a
       JOIN (
              SELECT DISTINCT projectid
                FROM projectsearch
               WHERE searchtext LIKE '%49%'
            ) b ON a.projectid = b.projectid

Он должен вернуть вам тот же набор значений projectid, что и ваш внутренний запрос. Он дает каждое значение projectid, которое соответствует searchtext для обоих условий поиска, даже если эти условия отображаются в разных строках project_search. Это то, что делает ваш запрос путем поиска GROUP_CONCAT() вывода.

Попробуйте создать индекс на (searchtext, projectid). Использование column LIKE '%sample' означает, что вы не сможете получить произвольный доступ к этому индексу, но два запроса в соединении все равно смогут сканировать индекс, что быстрее, чем сканирование таблицы. Чтобы добавить этот индекс, используйте эту команду.

 ALTER TABLE project_search ADD INDEX project_search_text (searchtext, projectid); 

Попробуйте сделать это в клиентской программе MySQL (например, phpmyadmin), а не непосредственно из вашей программы php.

Затем, используя клиент MySQL, протестируйте внутренний запрос. Посмотрите, сколько времени это займет. Используйте EXPLAIN SELECT ...., чтобы получить объяснение того, как MySQL обрабатывает запрос.

Возможно, ваши короткие ключевые слова возвращают смехотворно большое количество совпадений и каким-то образом перегружают вашу систему. В этом случае вы можете поместить предложение LIMIT 1000 или что-то в этом роде в конце вашего внутреннего запроса. Хотя это маловероятно. 17 кг это не много.

Если это не поможет, ваш рабочий сервер MySQL, вероятно, неправильно сконфигурирован или поврежден. Я бы на вашем месте позвонил в техподдержку вашего хостинга, как-то прошел бы агента службы поддержки (который ничего не знает, кроме как "перезагрузите компьютер" и прочей ерунды) и сказал бы им точное время, когда вы получили сообщение "ушел". Они смогут проверить журналы.

Совет: я уверен, что вы знаете о подводных камнях использования LIKE '%text%' в качестве поискового запроса. Он не масштабируется, потому что он не sargable: он не может произвольно обращаться к индексу. Если вы можете перепроектировать свою систему, это стоит вашего времени и усилий.

Вы можете TRY/CATCH проверить, получите ли вы более конкретную ошибку:

BEGIN TRY
    BEGIN TRANSACTION
        --Insert Your Queries Here--
    COMMIT
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();


    IF @@TRANCOUNT > 0
    ROLLBACK 

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

END CATCH

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

Попробуйте запустить этот запрос как с вашего сервера разработки, так и с рабочего сервера и проверьте, есть ли какие-либо различия:

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

Также проверьте текстовый файл my.ini (если это файл конфигурации), если они установлены на:

ft_stopword_file = ""

ft_min_word_len = 1

Как указано в моем EDIT, проблема заключалась не в запросе из исходного вопроса, а в некоторых других запросах, использующих параметр поиска. Каждый запрос имел следующую часть:

if(isset($filter['search'])) {
        $keywords = preg_split('/\s+/', trim($filter['search']));
        $field = 1;
        foreach($keywords as $keyword) {
            $join = $sql->select('project_search');
            $join->columns(["pid$field" => 'projectid']);
            $join->where(["LOCATE('$keyword', searchtext)"]);
            $join->group("projectid");
            $select->join(
                ["m$field" => $join],
                "m$field.pid$field = p.id"
            );
            $field++;
        }
    }

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