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