Как правильно разрулить FULLTEXT с типом таблиц InnoDB

InstantCMS 2.X

Возникают проблемы с поиском, с открыванием фото и др.

#1 21 января 2019 в 14:38
Началось с того, что обратил внимание на отказ фото открываться в фотоальбоме! Не хочет не только открываться на весь экран, но бывает не открываются даже в первое положение (когда открывается доступ к скачиванию).
Причем проблемы с фотоальбомом сопровождались ошибкой базы

[spoiler=Ошибка базыНевозможно отыскать полнотекстовый (FULLTEXT) индекс, соответствующий списку столбцов

SELECT i.*, MATCH(i.title) AGAINST ('>\"Дети\" <(дети)' IN BOOLEAN MODE) as `fsort`, u.nickname as `user_nickname`, u.is_deleted as `user_is_deleted`, u.avatar as `user_avatar`
FROM cms_photos i
INNER JOIN cms_users as u ON u.id = i.user_id
INNER JOIN cms_con_albums as al ON al.id = i.album_id
WHERE (i.id <> '14') AND (MATCH(i.title) AGAINST ('>\"Дети\" <(дети)' IN BOOLEAN MODE)) AND (i.is_private = '0') AND (al.is_approved = '1')
ORDER BY fsort desc
LIMIT 20[/spoiler]

С помощью Ris'а выяснилась закономерность, что если таблица cms_photos имеет тип MySam то все работает, а если InnoDB, тогда и начинаются проблемы! Вспомнил, что InnoDB не работают с FULLTEXT (а ведь этих индексов полно по базе, устанавливаются по умолчанию). Меняю индекс FULLTEXT на простой INDEX (в таблице индексов FULTEXT поменялся на BTREE) и фотографии начали открываться и при InnoDB!!!

Казалось бы проблема решена, но попробовав Поиском найти фото, опять столкнулся с проблемой (FULLTEXT) индекс! такая же проблема с Поиском возникает и с другими таблицами, если в них изменить FULLTEXT на INDEX/

Создается парадоксальная ситуация, когда База с типами таблиц InnoDB лучше работает без индексов FULLTEXT, но сам скрипт не может без них жить! Держать часть таблиц в MySam, а остальные в InnoDB не самая лучшая практика, теряется скорость работы и сложнее настроить конфигурацию MySQL

Вопрос к знатокам Как правильно разрулить FULLTEXT с типом таблиц InnoDB или что надо сделать, чтобы движек сайта нормально работал без индексов FULLTEXT?
#2 21 января 2019 в 18:34
Интересненько. Посижу послушаю.
Тем более в инсталляторе последней (2.11.0) версии рекомендуется выбирать InnoDB (раньше рекомендовалось MyISAM)…
#3 21 января 2019 в 18:51
В MySQL 5.6.4 вроде порешали вопрос с полнотекстом dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html
Возможно просто сами полнотекстовые индексы не объявлены в вашей бд. На 2.11.0 у кого-то я уже чинил базу и там не хватало индекса на title, хотя там был индекс (title, content). Проявлялось это именно в фотках и поисковике.
#4 21 января 2019 в 19:26
Loadырь,
Проводим эксперимент.
Ставим чистую INSTANTCMS2.11.0 на опенсервер. Модуль mysql — mysql5.8 (8.0.12).
Смотрим енджайн таблиц:

Видно без очков, что таблицы имеющие FULLTEXT индекс остались в MyISAM.
Таблица cms_photos тоже.

Далее конвертим cms_photos в InnoDB.
Тут же получаем характерную ошибку:
Ошибка в запросе БД:
  1. Невозможно отыскать полнотекстовый (FULLTEXT) индекс, соответствующий списку столбцов
  2.  
  3. SELECT i.*, MATCH(i.title) AGAINST ('\"98176037\"' IN BOOLEAN MODE) AS `fsort`, u.nickname AS `user_nickname`, u.is_deleted AS `user_is_deleted`, u.avatar AS `user_avatar`
  4. FROM cms_photos i
  5. INNER JOIN cms_users AS u ON u.id = i.user_id
  6. INNER JOIN cms_con_albums AS al ON al.id = i.album_id
  7. WHERE (i.id <> '1') AND (MATCH(i.title) AGAINST ('\"98176037\"' IN BOOLEAN MODE)) AND (al.is_approved = '1')
  8. ORDER BY fsort DESC
  9. LIMIT 20
  10. Последние вызовы:
  11.  
  12. cmsModel->GET() @ /system\controllers\photos\model.php : 123
  13. modelPhotos->getPhotos() @ /system\controllers\photos\frontend.php : 45
  14. photos->getPhotosList() @ /system\core\action.php : 29
  15. cmsAction->__call() @ /system\controllers\photos\actions\VIEW.php : 382
  16. actionPhotosView->getRelatedPhoto() @ /system\controllers\photos\actions\VIEW.php : 235
  17. actionPhotosView->run() @ /system\core\controller.php : 536
  18. cmsController->runExternalAction() @ /system\core\controller.php : 449
  19. cmsController->executeAction() @ /system\core\controller.php : 425
  20. cmsController->runAction() @ /system\controllers\photos\frontend.php : 15
  21. photos->route() @ /system\core\controller.php : 474
  22. cmsController->executeAction() @ /system\core\controller.php : 425
Мысль такая, что фултекст на InnoDB работает как-то не так, как на MyISAM.
#5 21 января 2019 в 19:29
Ris, после перевода на InnoDB добавьте полнотекстовый поиск ещё на title и посмотрите как будет работать.
у вас в запросе "MATCH(i.title)" такого индекса нет, есть на (title, content). добавьте на (title) и посмотрите
#6 21 января 2019 в 19:43

Ris, после перевода на InnoDB добавьте полнотекстовый поиск ещё на title и посмотрите как будет работать.
у вас в запросе "MATCH(i.title)" такого индекса нет, есть на (title, content). добавьте на (title) и посмотрите

Loadырь
Да, помогло.
Я где-то слышал, что в InnoDB MATCH требует полного соответствия полей, перечисленных в индексе.
#7 21 января 2019 в 19:44
Ага и только что в этом убедились
#8 21 января 2019 в 19:48

такого индекса нет, есть на (title, content)

Loadырь
этот индекс означает поиск одновременно по двум полям!
Loadырь
Зачем создавать повтор, пусть даже с одним полем для поиска?
#9 21 января 2019 в 19:50

Зачем создавать повтор, пусть даже с одним полем для поиска?

vikont
Затем, что

в InnoDB MATCH требует полного соответствия полей, перечисленных в индексе.

Ris
#10 21 января 2019 в 19:57

Зачем создавать повтор, пусть даже с одним полем для поиска?

vikont
Можно делать не повтор, а отыскать и поправить запрос в БД на MATCH(i.title, i.content), тогда всё будет чётко smile
#11 21 января 2019 в 20:01

Ris:
в InnoDB MATCH требует полного соответствия полей, перечисленных в индексе.

Ris
Вот и получается, что на каждый запрос надо свой индекс
Например в таблице cms_con_albums для работы с фотками нужен индекс (title), а поиск уже требует (title, content) делаем второй индекс и перестают работать фотки… и какой отсюда выход?
#12 21 января 2019 в 20:05

Можно делать не повтор, а отыскать и поправить запрос в БД на MATCH(i.title, i.content), тогда всё будет чётко

Loadырь
И так перелопатить весь движок Инстанта… где б столько мозгов взять… facepalm
У Fuze не займешь, заняты на 200%...
Получается движек еще не полностью готов к 100% переходу на InnoDB
#13 21 января 2019 в 20:15
vikont,
Да повесьте Вы еще один индекс. Вам жалко что ли?
#14 21 января 2019 в 20:31

vikont,
Да повесьте Вы еще один индекс. Вам жалко что ли?

Ris
Вы читали выше? Повесил и фотки уже не открываются.
#15 21 января 2019 в 20:32
Пришло время пояснить за что боремся.....
Мой VPS имеет вебсервер NGINX+php-frm+MariaDB 10.3.7 базы с таблицами InnoDB
Это дало снижение загрузки самых тяжелых страниц (при не загруженном сервере) до 0,8 сек, при повторном обновлении страницы, до 0,7 сек, при обновлении страницы со сбросом кеша (CTRL+F5), до 0,6 сек!
А вот когда удаляются индексы FULLTEXT, то эти показатели падают еще минимум на 0,1 сек. Легкие страницы грузятся вообще с 0,08ххх и меньше.

Кроме этого, когда все таблицы типа innoDB сама база работает быстрее и более корректно, как в мужском коллективе, а смешанная база… это как коллективе появилась хотя бы одна женщина… laughИ управляется тяжелее и работает не предсказуемо.

Вот такие аргументы в пользу InnoDB и Fuze не зря сделал ставку на него.
Вы не можете отвечать в этой теме.
Войдите или зарегистрируйтесь, чтобы писать на форуме.
Используя этот сайт, вы соглашаетесь с тем, что мы используем файлы cookie.