Если только через IN, но он работает значительно медленнее.
Сам алгоритм такой: получаю от сфинкс список id и записываю их во временную таблицу, затем её джойню с полной таблицей. Чем больше ID во временной таблице, тем дольше выполняется выборка в основную таблицу FULL.
260000 ID за 6 минут пока лучший результат, наверное если перенести БД на SSD, получится около 3ёх минут.
edogs, спасибо, у меня CP1251, база FULL весит 80Гб.
Этот запрос дольше, 12ая минута уже идёт, так и не выполнился.
А может через my.cnf можно повлиять? Как-то задействовать 12Гб памяти? Не понимаю в тюнинге MySQL, тестировал разные значения, но прироста в скорости не почувствовал (
Размер - 80Гб
Id - int (10)
Name - varchar (70)
Задача - полнотекстовый поиск по полю (с морфологией). Использую Sphinx, затем полученные id джойню, почему Join настолько долгий не понимаю ((
процесс MySQL считывает с диска, но скорость чтения очень слабая, всего 1-1.5Мб в сек. Почему так слабо не понимаю, больше никаких процессов нет.
edogs, спасибо, формат был один и тотже, изменил запрос, стало в 2 раза быстрее, но всёравно долго :( Около 6 минут.
Или выше скорости не добиться? Если на SSD базу поставить, будет значительно быстрее?
netwind, спасибо, force index пробовал, не помогло. ft_min_word_len это для FULLTEXT было, пробовал его, но не устроило, вот решил на sphinx переидти.
ivan-lev, размер Primary 1Гб. Ограничения на индексы наверное по умолчанию, не знаю как их можно указать в конфиге. Попробовал загонять id в memory таблицу, затем join'ить. Так гораздо быстрее, но почему-то результат в файл медленно сохраняется и в phpmyadmin выводится долго.
Запрос:
SELECT name FROM tmp_tabl JOIN FULL WHERE FULL.id=tmp_tabl.id
Отображает строки 0 - 29 (8,688 всего, запрос занял 0.2990 сек.)
но по факту в phpmyadmin выводит дольше, около 40 секунд. С чем это связано?
Например такой запрос:
SELECT name FROM tmp_tabl JOIN FULL WHERE FULL.id=tmp_tabl.id INTO OUTFILE 'путь'
выполняется 40 секунд, а обычный селект пишет запрос занял 0.2990 сек
SELECT SQL_NO_CACHE пробовал, толку никакого. Результаты запроса не понадобятся, кеш не нужен, но его отключение не ускорило выполнение запроса.
Настройки MySQL:
skip-locking max_connections=2 max_allowed_packet=32M query_cache_size=2M table_cache=256 tmp_table_size=2M thread_cache_size=128 thread_concurrency=64 myisam_max_sort_file_size=300M myisam_sort_buffer_size=200M key_buffer_size=3700M read_rnd_buffer_size=320M sort_buffer_size=240M ft_min_word_len =1 [myisamchk] key_buffer = 3700M sort_buffer_size = 30M ft_min_word_len =1
Funaki, спасибо, но тоже самое. Когда много ID, долго выполняется.
netwind, пробовал разбивать на несколько запросов, поделив список ID по 1000. Но в совокупности получается также долго, как и один запрос.
Всем спасибо за внимание к проблеме.
Перенёс на postgresql, но проблема осталась :( видимо мало памяти.
Есть мысль держать в sphinx атрибуты для того, чтобы не делать выборку по ID из базы. Но тут всё упирается в память, ведь атрибуты хранятся в памяти.
Атрибуты varachar(100), есть возможность существенно сократить размер?
Ищу в документации, где-то видел что в Sphinx можно длину атрибутам задавать и тем самым уменьшить размер.
1) 95,2Гб
2) Отображает строки 0 - 29 (1,151,918,377 всего, запрос занял 0.0005 сек.)
3) Core I5-760, 10ГБ ОЗУ
Записываются в текстовый файл.
Милованов Ю.С, делить смысла нету, так как полная база нужна для общих запросов.
В общей таблице ищем общие запросы.
Вот результат:
source FULL { type = mysql sql_host = localhost sql_user = root sql_pass = 12345 sql_db = ZP sql_port = 3306 # Для ускорения работы прописываем путь до MySQL-го UNIX-сокета (чтобы # операции с БД происходили не через TCP/IP стек сервера) sql_sock = /var/run/mysqld/mysqld.sock sql_query_pre = SET NAMES utf8 sql_query_pre = SET CHARACTER SET utf8 sql_query = \ SELECT * \ FROM FULL }
Sphinx то отрабатывает нормально, это уже потом MySQL запрос долго выбирает данные по ID.
Возможно, хотел поделить список ID по 1000 и делать N запросов по 1000 ID, но решил всётаки тему создать и спросить почему всётаки такая медленная выборка по большому списку ID. Может в конфиге my.cnf что-то подкрутить?
Кстати может можно как-то в Sphinx сделать чтобы он выводил вместо ID нужное мне поле и тогда не надо было бы делать дополнительный запрос в таблицу MySQL?
В INT максимальное значение 4294967295 (10 знаков ), об этом не знал, но всёравно, если поле описано как INT(13), это ведь не должно быть причиной такого медленного поиска. В INT 1 миллиард записей поместился.
Смысла нет, иначе придётся искать потом по каждой таблице.
Вот Explain по другой таблице, структура таже, индексы теже, только запрос сделал на 167 ID чтобы долго не ждать, запрос выполнился за 0,05сек, но индекс не используется:
ivan-lev, да, нужно вытащить данные по всем 30к ID. Force Index и Use Index не помогают, explain показывает что индекс не используется. Данные в таблице не изменяются, нужно только выборку по ней делать.
Милованов Ю.С, нужно сделать быстрый полнотекстовый поиск. Пробовал Fulltext, но тоже долго ищет. Вот перешёл на Sphinx, но он выдаёт только ID нужных мне записей и приходится делать запрос в БД MySQL для выборки нужных полей с этими ID.
К Sphinx притензий нет, ID выводятся достаточно быстро (секунд 5-10) но вот выборка по этим ID из MySQL очень долгая.
