limit - тормоза, full table scan

berkut

Новичок
limit - тормоза, full table scan

Вообщем есть таблица с ~11000 записей, на диске занимает ~1Гб
И тут не понятная проблема с выборкой - LIMIT 1, 10 - летает,
LIMIT 100, 10 - подтормаживает, LIMIT 10000, 10 - жуть тормозит.
Сделал подобную тестовую таблицу:
Код:
CREATE TABLE `test_count` (
  `id` mediumint(9) unsigned NOT NULL auto_increment,
  `field2` text NOT NULL,
  `field3` text NOT NULL,
  `field4` text NOT NULL,
  `field5` longblob,
  `field6` longblob,
  `field7` longblob,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM
Запрос:
Код:
SELECT * FROM test_count LIMIT {VAR}, 10
Результаты на моей машине:
{VAR} = 1 - 0,2 сек.
{VAR} = 100 - 0,03 сек.
{VAR} = 1000 - 0,09 сек.
{VAR} = 10000 - ~23 сек.
Проблема в том, что для поиска 10000 строки
муська сканирует
всю таблицу, вместо того, что-бы использовать
индекс id.
unique key по id + use index не спасают.
Сделал такой костыль:
Код:
SELECT id FROM test_count LIMIT 10000, 10;
+
SELECT * FROM test_count
WHERE id IN (RESULTS_FROM_PREVIOUS_QUERY);
эти запросы выполняются мгновенно
ещё как вариант:
Код:
SELECT * FROM test_count, (SELECT id FROM test_count ORDER BY id LIMIT 10000, 10) t 
WHERE test_count.id=t.id;
но это всё не красиво и не оптимально.
Как всё-таки заставить муську при select * ... limit использовать индекс
 

phprus

Moderator
Команда форума
всю таблицу, вместо того, что-бы использовать
индекс id.
С чего ты взял, что этот индекс должен использоваться? Добавь в запрос например сортировку по этому индексу и тогда он будет использоваться при выборке.
 

Serg Karpenko

Новичок
Как всё-таки заставить муську при select * ... limit использовать индекс
Ну судя по фразе из LIMIT Optimization

If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

никак.
 

berkut

Новичок
phprus сортировку по id добавлял, толку нет. ORDER BY id LIMIT 10000, 10 - выполняется также ~23-32 сек.
Код:
SELECT * FROM test_count 
ORDER BY id LIMIT 10000, 10;
explain:
Код:
| id | select_type | table          | type  | possible_keys | key         | key_len | ref     | rows    | Extra |
+----+-------------+------------+-------+---------------+---------+---------+---
|  1 | SIMPLE       | test_count | index | NULL              | PRIMARY |       3     | NULL | 11193 |       |
-~{}~ 07.10.06 18:14:

Serg Karpenko чессно не понял...
Если ты выбираешь только несколько рядов с LIMIT, Mysql, в некоторых случаях, использует индексы, не смотря на то, что желательнее было-бы использовать полное сканирование таблицы
Как-то не стыкуется с моим случаем, всё как раз наоборот
 

Serg Karpenko

Новичок
сортировку по id добавлял, толку нет
А чего вы хотите? Индекс в этом случае используется
для сортировки, а не для ограничения набора записей.

Привязывайтесь к id и будет вам скорость:
к примеру - where id > {VAR} order by id limit 10
 

berkut

Новичок
Serg Karpenko вообще-то
Код:
select * from test_count where id > 10000 order by id limit 10
и
Код:
SELECT * FROM test_count LIMIT 10000, 10;
это логически разные запросы. один другого не заменит
 

Serg Karpenko

Новичок
Мда, цитата выше получилась неудачной.. :(

в общем я хотел сказать что при select * limit
индекс будет пользоваться только для сортировки.


Serg Karpenko вообще-то

code:

select * from test_count where id > 10000 order by id limit 10
и

code:

SELECT * FROM test_count LIMIT 10000, 10;

это логически разные запросы. один другого не заменит
Я это понимаю. Просто использование индексов при select * в данном случае даст выигрыш
только при использовании where..

Я скорее имел ввиду:
select * from test_count where id > (select id from test_count order by limit 10000,1) order by id limit 10
хотя у вас уже выше есть похожий вариант..
 

Igor aka TiGR

Новичок
С тех пор в оптимизации LIMIT были исправлены несколько ошибок. Вполне возможно, что на более свежих версиях всё работает нормально.

-~{}~ 08.10.06 15:16:

И, кстати, вот ещё:
http://dev.mysql.com/doc/refman/5.1/en/how-to-avoid-table-scan.html

Там описано несколько приёмов как избежать полного сканирования таблицы.

HTH
 

berkut

Новичок
Попробывал на 5.0.6-beta, понятно что не самая стабильная и последняя версия, но ситуация аналогичная. Странно как-то всё. И force index не помогает
 

zip111

Новичок
Да, то что тормозит лимит - это факт.

Самый простейший способ, которым я пользуюсь в случае лимитирования вывода в объемных таблицах (в моем случае - почти 100 миллионов записей) - вместо Limit использую between по первичному ключу.

результат:
select * from campaign limit 23089333, 30
Время выполнения - 89 секунд

select * from campaign where id between 23089333 AND 23089363
время выполнения - 0,02 секунд
 

akd

dive now, work later
Команда форума
zip111, это только если ты уверен, что у тебя нет "дырок".
 

zip111

Новичок
Не вижу принципиального различия. Что там надо закрывать "дырки", что там. :)
 

kruglov

Новичок
А смысл вообще в несортированном лимите? Тем более, что даже если это постраничное листание, база легко в промежутках между листаниями может добавить запись с Id=1000 между 1 и 3 вместо удаленной 2-й.

-~{}~ 23.04.08 14:23:

p.s. Чем-то напоминает классический вопрос "как мне получить 10-ю запись таблицы"
 

zip111

Новичок
какая разница, даже если и добавит. Ключи то тоже перепишутся. А в случае с битвин поиск то по ключу и идет.
 

Breeze

goshogun
Команда форума
Партнер клуба
ну попробуй такой костыль :)
все лучше, чем вложенный селект

SELECT t2.* FROM test_count t1 LEFT JOIN test_count t2 ON t1.id=t2.id LIMIT 10000, 10
 

kruglov

Новичок
zip111
Какие ключи перепишутся? Автоинкрементрый PRIMARY? Ну да, и что?

Мы про "SELECT * FROM test_count LIMIT 10000, 10" говорим или про какие-то запросы, где уже упоминаются поля, на которые индексы стоят?
 

zip111

Новичок
kruglov
ну так у автора топа проблема в скорости вывода при лимите. запрос с битвин который я привел явно лучше чем костыли с джоинами и подзапросами
 
Сверху