Оптимизация FULLTEXT в MySQL

Имеется таблица с 2.4млн записей. Запрос SELECT COUNT(id) FROM table WHERE MATCH (filename) AGAINST ('someword' выполняется за 30сек, если подходящих записей около 2 тысяч.
Получение числа всех записей необходимо для пагинации. Есть варианты ускороить этот запрос как минимум в 10 раз без применения сфинкса?

Пока переделал временно на SELECT id FROM table WHERE MATCH (filename) AGAINST ('someword' LIMIT 100, но это не тот выход, который надо найти.

)) построить свой индекс всех filename c фиксацией какой filename сколько раз встречается и сохранить этот индекс в виде отдельной таьлицы

Лучше установить Sphinx и не парить себе мозг.

Такой тяжелый запрос лучше закешировать.
И если в системе 2.4 мли записей в которые еще и фуллтекст то кешировать всё, что плохо лежит.

Высылайте оперативку, закеширую всё.

Эту проблему еще лет 7-8 назад встречал. Можно ускорить (но не на порядок!) выделяя MySQL больше памяти (см. параметры key_buffer_size и т.д. в конфиге), или посмотреть какую кодировку используете, если UTF-8, то отказаться от нее (если позволяет задача, конечно), и т.д., но это всё мелочи, не решение.
Потому что FULLTEXT будет все больше и больше тормозить по мере увеличения количества записей в таблице, и ничего с этим не поделать.

Выход только один — строить свой индекс, оптимизированный под природу хранимых данных и под текущие запросы.
Совет vladmax-а не так уж плох.

Мое мнение, FULLTEXT в MySQL вообще хорошо бы запретить для разработчиков... кривоватый инструмент сам по себе, с устаревшей реализацией, попытка сделать универсальное высокоуровневое решение.
Неспроста в InnoDB не перенесли FULLTEXT индексы.
Могу лишь догадываться, что FULLTEXT индексы были предназначены лишь для очень небольших наборов данных, а потом их просто не удалили, оставили как заявленную фичу MySQL в маркетинговых целях. Хотя могу и ошибаться, конечно.

Цитата ("tvv"):

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

Насколько быстрее будет работать со сфинксом при прочих равных?

Цитата ("tvv"):

Совет vladmax-а не так уж плох.

Он плох для данной задачи. Тем, что внутри MATCH может быть произвольный набор слов.

Цитата ("Lisio"):

Насколько быстрее будет работать со сфинксом при прочих равных?

Насчет сфинкса не скажу, очень зависит от Ваших данных, от конфигурации и т.д. В несколько секунд, по-идее, должны вложиться.

Если построите свой индекс, оптимизированный под данные и под запросы, то будет десятые доли секунд, по крайней мере обычно такие требования выдвигаются к подобным модулям поиска, и успешно реализуется (я делал это в ряде проектов, причем даже в InnoDB, который тормознее MyISAM в таких случаях). Из недостатков — это счастье будет за счет создания очень избыточной денормализованной схемы данных.

Цитата ("Lisio"):

Он плох для данной задачи. Тем, что внутри MATCH может быть произвольный набор слов.

То, что внутри MATCH, Вы будете предварительно парсить, и потом для каждой полученной лексемы будете делать запрос к своему индексу.
По сути, делаете то же, что и движок MyISAM с FULLTEXT индексом, только уже в своей структуре данных (в своем индексе).

Цитата ("tvv"):

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

Данные представляют из себя названия в виде последовательности слов, каждое слово может выглядеть так: part08 или file12.
В форме поиска на сайте вводиться может что угодно.
Даже не представляю, как для такого чуда создать свой индекс быстро и безболезненно, причем это не тот проект, на который хотелось бы затратить больше пары часов.

Универсального решения тут нет. И вряд ли эта задача решаема за 2 часа, если не использовать какого-либо готового решения.

Прежде всего, сколько вообще возможно слов, которые используются? Каков процент уникальных, много ли повторяющихся. От этого зависит выбранная структура данных.
Если составить словарь используемых слов (самый примитивный, вида "id слова" и "слово", чтобы потом работать не с текстами, а с целыми числами), сколько их примерно получится?

Также нужно знать какие именно данные, какие кодировки, какая минимальная и максимальная длина слова, "part08 или file12" — значит ли это, что большинство слов представляют собой комбинацию текст+цифра?
Какие запросы преобладают (например, запросто может оказаться что 99% запросов состояит из одного слова), нужно ли делать запросы с объединением по И, или по ИЛИ, или с отрицаниями.
Соответственно этому будем строить индекс.

Цитата ("Lisio"):

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

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

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

Да, без проблем.

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

Именно поэтому и будет куча вопросов по природе данных, а еще лучше — получить дамп этой таблицы, чтобы я посмотрел данные сам и избавил Вас от лишних вопросов.
Если хотите, можете хоть сейчас прислать, я потом позже гляну.

В текущем виде, если удалить индексы, мегабайт 200 будет весить в архиве.

Ничего страшного. Есть куда выложить? Я скачаю.

Ушло в личку, около 70мб в tar.gz