Здравствуйте, коллеги!
Есть база которая несколько раз в сутки переписывается целиком (частями)
После каждого изменения (допустим удалил 2.000.000 и добавил 1.500.000) полностью перебираю всю базу.
Запрос выглядит так:
select
tp.manufacturer,
tp.manufacturer_number,
(
select
cost
from
b_price as tp1
where
tp1.search = tp.search and
tp1.manufacturer = tp.manufacturer
order by tp1.cost asc
limit 1
) as p1,
(
select
cost
from
b_price as tp1
where
tp1.search = tp.search and
tp1.manufacturer = tp.manufacturer
order by tp1.cost asc
limit 1, 1
) as p2
from
b_price as tp
group by tp.search
limit 0, 10000;

search, manufacturer - это индексы

Запрос limit 0, 10000 - занимает 1-3 секунды. А запрос limit 1000000, 10000 занимает около 4-х минут. И каждое последующее обращение занимает больше время чем предыдущее.

Count best: Start:0 Count:5975901 Limit: 10000 Time:1 [OK]
Count best: Start:10000 Count:5975901 Limit: 10000 Time:3 [OK]
Count best: Start:20000 Count:5975901 Limit: 10000 Time:3 [OK]
Count best: Start:30000 Count:5975901 Limit: 10000 Time:5 [OK]
Count best: Start:40000 Count:5975901 Limit: 10000 Time:8 [OK]
Count best: Start:50000 Count:5975901 Limit: 10000 Time:10 [OK]
Count best: Start:60000 Count:5975901 Limit: 10000 Time:11 [OK]
Count best: Start:70000 Count:5975901 Limit: 10000 Time:12 [OK]
Count best: Start:80000 Count:5975901 Limit: 10000 Time:14 [OK]
Count best: Start:90000 Count:5975901 Limit: 10000 Time:14 [OK]
Count best: Start:100000 Count:5975901 Limit: 10000 Time:16 [OK]

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

Благодарю всех за внимание, и надеюсь на понимание.

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

Хорошая железка у вас.
За "около 4-х минут" прожевать более 2 млн запросов, практически эпические over 9000 в секунду. Что там стоит в качестве процессора, так молотить в однопоточном-то режиме?

Поясняю: подзапрос в селекте выполнятся для каждой строки результата отдельно. У вас два подзапроса - на банальный limit 10 offset 0 выполняется уже 21 запрос. Запрос limit 10 offset 100, как уже сказал DelphinPRO, сначала выберет 110 записей, потом отбросит первые 100 и отдаст 10. Т.е. сделает уже 221 селект.

Общий совет для больших offset'ов - не делать большие offset.
Если надо для чего-то перебрать всю таблицу - читайте кусками по какому-то диапазону, которых можно прочитать быстро. Часто используется первичный суррогатный ключ - вычитывается максимальное значение, диапазон от 0 до этого значения делится на куски и обходятся простым where id >= :range_min and id <= :range_max. Ну будет не 10000 элементов, а только тысяча - и ладно. Зато чтение по первичному ключу работает быстро, не в пример offset'у

[quote=Мелкий][/quote]
Железо мощное (8 ядер 32 памяти)

Благодарю всех за внимание. Решение нашел

Дублирую таблицу, и беру сразу все не разбивая (4-6 минут на все)

Не, не мощное. Мощное под СУБД - это хотя бы сотня гигов RAM. Но маркетинг меня не интересует. Что всего 5 лямов записей целиком помещаются в памяти и так очевидно. А количество ядер для этой конкретной задачи бесполезно - один коннект mysql исполняется только на одном ядре.
Значит у процессора неплохая производительность на ядро. И вот именно ядро меня и заинтересовало для статистических величин.

Процессор - Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz 800.000 Mhz X 8