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]

всю таблицу, вместо того, что-бы использовать
индекс 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]

Хм... А какая версия MySQL используется?

 

[berkut]

mysql 4.1.8

 

[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]

zip111, это только если ты уверен, что у тебя нет "дырок".

 

[zip111]

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

 

[kruglov]

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

-~{}~ 23.04.08 14:23:

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

 

[zip111]

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

 

[Breeze]

ну попробуй такой костыль
все лучше, чем вложенный селект

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" говорим или про какие-то запросы, где уже упоминаются поля, на которые индексы стоят?

 

[Breeze]

А смысл вообще в несортированном лимите?

кстати
+1

 

[zip111]

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