тормозит limit m, n

[alex77]

тормозит limit m, n

Здравствуйте.

Есть таблица, в которой около 32000 строк.
по ней сделана постраничная навигация через limit m, n
и чем больше m, тем медленнее работает выборка.
При этом, если в выборке не участвуют поля типа text то работает намного быстрее, но всё равно медленно (15ms).
Если имеет значение, размер таблицы 468М.

Как ещё ускорить выборку?

 

[Wicked]

для начала правильно задать вопрос: привести show create table таблицы, запрос и его explain

 

[c0dex]

15 ms ему много... кто-то зажрался.

 

[флоппик]

http://www.facebook.com/?ref=logo#!/note.php?note_id=206034210932&id=102841356695

 

[Фанат]

бесят эти сосальные сети, которые требуют авторизации для показа всякого гна

 

[fixxxer]

хм, у меня без авторизации открывается

но на ссылку нажимал с опасением, да

 

[dimagolov]

есть подозрение, что там не 15ms, а 15s

вообще вместо limit n,m нужно:
1. отказаться от выбора страницы № пять тысяч шестьсот восемьдесят три, а оставить только первая - предидущая - следующая - последняя
2. делать индексы по критериям отбори и id и отбирать на вместо limit n, m where ... and ld > lim order by ..., id limit n

 

[Фанат]

а, это у меня NoScript. прошу прощения. привык к ссылкам в контакт

dimagolov, ну так последняя все равно будет.
Вообще, я давно думаю об эти вещи.
Надо, наверное, кэширование прикручивать, етажное.
Все равно там в хочте ничего не меняется, а читают его только роботы

 

[dimagolov]

последняя? order by ... desc limit n. Кого там будет волновать,что отобразили не n-3, а n результатов?

 

[Фанат]

Хм. идея, кстати, хорошая. до середины сортировать прямо, а от середины - обратно!

 

[fixxxer]

BTW, такого рода ссылки (?from_id=12345) более констистентны с точки зрения их распространения по сети: контент по номеру страницы сто раз изменится, а по ID - нет.

 

[Фанат]

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

 

[fixxxer]

Кстати, если нумеровать с конца, то можно спокойно хранить в базе номер страницы и не отказываться от их нумерации. При наличии индекса (page_number, id) со скоростью будет все прекрасно =) да и page_number в общем то достаточно, 10-20 элементов не проблема отсортировать и filesort-ом (который в данном случае всегда влезет в sort_buffers и будет memory sort-ом).

Пересчитывать придется только при удалении, что по идее совсем нечастая операция.

 

[alex77]

CREATE TABLE `cs_view_events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` int(10) unsigned NOT NULL DEFAULT '0',
`source` varchar(255) DEFAULT NULL,
`short_message` varchar(255) DEFAULT NULL,
`message` text,
`trace` text,
`get` text,
`post` text,
`cookie` text,
`server` text,
`session` text,
`created` int(10) unsigned DEFAULT NULL,
`user_id` int(10) unsigned DEFAULT NULL,
`registered_user_id` int(10) unsigned DEFAULT NULL,
`remote_ip` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128309 DEFAULT CHARSET=utf8

-~{}~ 23.08.10 23:13:

mysql> SELECT id FROM `cs_view_events` ORDER BY `id` DESC
-> LIMIT 80000, 10;

10 rows in set (0.19 sec)

mysql> explain SELECT id FROM `cs_view_events` ORDER BY `id` DESC
-> LIMIT 80000, 10;
+----+-------------+----------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | cs_view_events | index | NULL | PRIMARY | 4 | NULL | 54999 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)

-~{}~ 23.08.10 23:15:

mysql> SELECT * FROM `cs_view_events` ORDER BY `id` DESC
-> LIMIT 80000, 10;

10 rows in set (0.44 sec)

mysql> explain SELECT * FROM `cs_view_events` ORDER BY `id` DESC
-> LIMIT 80000, 10;
+----+-------------+----------------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+------+-------+-------+
| 1 | SIMPLE | cs_view_events | index | NULL | PRIMARY | 4 | NULL | 54999 | |
+----+-------------+----------------+-------+---------------+---------+---------+------+-------+-------+
1 row in set (0.00 sec)

-~{}~ 23.08.10 23:22:

SELECT * FROM `cs_view_events` ORDER BY `id` DESC
LIMIT 50000, 10;

250ms

SELECT * FROM `cs_view_events`
INNER JOIN (SELECT id j_id FROM `cs_view_events` ORDER BY `id` DESC LIMIT 50000, 10) t
ON id = j_id
ORDER BY `id` DESC;

109ms

-~{}~ 23.08.10 23:27:

Да, вот тут http://habrahabr.ru/blogs/mysql/44608/ читал про навигацию , но мне надо было сделать номера страниц.

-~{}~ 23.08.10 23:46:

переделал таблицу в тип MyISAM результат такой

SELECT id FROM `cs_view_events` ORDER BY `id` DESC
LIMIT 50000, 10;

15ms

SELECT * FROM `cs_view_events` ORDER BY `id` DESC
LIMIT 50000, 10;

0.3s


SELECT * FROM `cs_view_events`
INNER JOIN (SELECT id j_id FROM `cs_view_events` ORDER BY `id` DESC LIMIT 50000, 10) t
ON id = j_id
ORDER BY `id` DESC;

16ms

SELECT * FROM `cs_view_events`
INNER JOIN (SELECT id j_id FROM `cs_view_events` ORDER BY `id` DESC LIMIT 80000, 10) t
ON id = j_id
ORDER BY `id` DESC;

31ms

-~{}~ 23.08.10 23:48:

SELECT * FROM `cs_view_events`
INNER JOIN (SELECT id j_id FROM `cs_view_events` ORDER BY `id` DESC LIMIT 300000, 10) t
ON id = j_id
ORDER BY `id` DESC;

94ms

-~{}~ 23.08.10 23:52:

SELECT * FROM `cs_view_events`
INNER JOIN (SELECT id j_id FROM `cs_view_events` ORDER BY `id` DESC LIMIT 1360000, 10) t
ON id = j_id
ORDER BY `id` DESC;

422ms

 

[dimagolov]

то можно спокойно хранить в базе номер страницы

fixxxer, а если нужно много критериев сортировки?

-~{}~ 23.08.10 15:01:

alex77, раз читал, но тебе нужно, то предложи разработчикам MySQL патч, который позволит определять выдаваемые в выборку order by m, n строки по индексу не перебирая все m + n строк, как это делает мускль сейчас.

 

[fixxxer]

> fixxxer, а если нужно много критериев сортировки?

тогда не получится

-~{}~ 23.08.10 22:32:

Вообще, "много критериев сортировки" это та задача, для которой mysql вообще слабо годится. Особенно если критерии комбинируются в произвольном порядке =)