Mysql> SELECT * FROM messages ORDER BY id LIMIT 1 OFFSET 500000 Mysql> SELECT * FROM messages ORDER BY id LIMIT 1 OFFSET 100000 Mysql> SELECT * FROM messages ORDER BY id LIMIT 1 OFFSET 10000 Mysql> SELECT * FROM messages ORDER BY id LIMIT 1 OFFSET 1000 Mysql> SELECT * FROM messages ORDER BY id LIMIT 1 OFFSET 100 Mysql> SELECT * FROM messages ORDER BY id LIMIT 1 OFFSET 10 Now let's measure how long it takes to select a single row at a time at various offsets: mysql> SELECT * FROM messages ORDER BY id LIMIT 1 Having a table with only a single numeric primary key column won't highlight the issues as well. Note: we need a table of some significant size like this in order to illustrate the performance degradation of OFFSET. The table ended up with about 741MB of data: mysql> SELECT round((data_length + index_length) / 1024 / 1024) AS `Size (MB)` This helps provide some disk I/O optimization when querying rows in a range because many of them will be stored in the same pages. ![]() So when you query a row by its clustered index, InnoDB will look for the b-tree leaf that contains that value, and then it's inexpensive to read the rest of the row data. But clustered indexes are special because they also store the rest of the row's data alongside it in the same page. ![]() Like all other InnoDB b-tree indexes, clustered indexes will store values that are close together value-wise on the same page on disk. , ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) f) temp īecause id is the primary key it becomes the InnoDB table's clustered index. , ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e , ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d , ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c , ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b Sed cursus in eros sit amet interdum.' FROM ( SELECT 1 FROM ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia curae Aenean orci mi, consectetur sed turpis sed, consequat tempor nisi. Curabitur rutrum, lorem sit amet vulputate ultricies, velit odio ultrices dui, sed volutpat lorem felis vitae nibh. Nam viverra felis ac ex convallis, in congue nunc ultrices. Morbi consectetur, lorem in pulvinar tincidunt, augue est cursus ipsum, sed dapibus neque sapien id libero. Integer aliquam ornare velit, auctor tempus erat ultrices ut. SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. ![]() ![]() To illustrate the problem, let's create a simple table and fill it with 1,000,000 rows of non-trivial size: CREATE TABLE messages Large limit offsets degrade the performance of most databases, but it is especially egregious in MySQL.
0 Comments
Leave a Reply. |