[MySQL] Медленный join по миллиарду записей

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

Насколько такой вариант изменит результат ?

2 миллиарда = какой размер, какие типы данных (text, varchar,...)

Попробуйте задачу объяснить, возможно есть и другое решение.

Что происходит с диском/памятью в момент выполнения? (munin или что-то такое должно быть)

1) Поля ID в tmp и full должны быть строго одинаково объявлены - допустим bigint(20) - это даст работать индексам

2) Запрос должен быть таким

select sql_no_cache name from tmp left join full on tmp.id=full.id into outfile 'c:/full.txt';

в Вашем варианте Вы делаете декартово произведение таблиц - а с 2 миллиардами это офигенно много (как у Вас вообще сервер не лег), потом фильтруете результат... в нашем Вы присоединяете из full только нужные Вам данные, поэтому все должно быть быстро.

Размер - 80Гб

Id - int (10)

Name - varchar (70)

Задача - полнотекстовый поиск по полю (с морфологией). Использую Sphinx, затем полученные id джойню, почему Join настолько долгий не понимаю ((

процесс MySQL считывает с диска, но скорость чтения очень слабая, всего 1-1.5Мб в сек. Почему так слабо не понимаю, больше никаких процессов нет.

edogs, спасибо, формат был один и тотже, изменил запрос, стало в 2 раза быстрее, но всёравно долго :( Около 6 минут.

Или выше скорости не добиться? Если на SSD базу поставить, будет значительно быстрее?

Потому что если данные разбросаны у Вас по всей таблице, то что бы получить 263 тысячи - ему нужно сделать кучу позиционирований по диску. Индексы подсказывают ГДЕ брать информацию, но что бы ее взять - мускул все должен спозиционироваться в нужную точку на диске.

1) В int влезает 2 миллиарда и 150 миллионов записей примерно. Скоро упретесь в размер поля, учтите это. unsigned int на какое-то время поможет если упретесь.

2) char (70) может сильно ускорить выборку, но может и сильно замедлить. Зависит от Ваших данных, попробуйте оба варианта.

3) Если у Вас там чисто русский-английский но при этом Utf-8, то подумайте о переходе на cp1251 - размер базы на диске станет меньше - работать будет быстрее.

Именно для Вашей задачи возможно лучше будет даже

select sql_no_cache name from full where full.id in ( select tmp.id from tmp ) into outfile 'c:/full.txt';

---------- Добавлено 04.03.2013 в 04:32 ----------

edogs, спасибо, у меня CP1251, база FULL весит 80Гб.

Этот запрос дольше, 12ая минута уже идёт, так и не выполнился.

А может через my.cnf можно повлиять? Как-то задействовать 12Гб памяти? Не понимаю в тюнинге MySQL, тестировал разные значения, но прироста в скорости не почувствовал (

Мне SSD очень сильно помог.

Но вас не спасет, вам надо решить сначала проблему.

Без Джойна - совсем никак не переделать?

Если только через IN, но он работает значительно медленнее.

Сам алгоритм такой: получаю от сфинкс список id и записываю их во временную таблицу, затем её джойню с полной таблицей. Чем больше ID во временной таблице, тем дольше выполняется выборка в основную таблицу FULL.

260000 ID за 6 минут пока лучший результат, наверное если перенести БД на SSD, получится около 3ёх минут.

А зачем за раз получать 260000 записей?

может как-то сам процесс разбить?

Пробовал разбивать, но быстрее не стало.

Может кто подскажет как такой запрос ещё оптимизировать можно:

select sql_no_cache name from tmp left join full on tmp.id=full.id into outfile 'c:/full.txt';

Или в конфигурации MySQL что-то поправить для ускорения?