{"id":91048,"date":"2020-08-08T01:42:02","date_gmt":"2020-08-07T23:42:02","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy"},"modified":"2020-08-08T01:42:02","modified_gmt":"2020-08-07T23:42:02","slug":"ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","title":{"rendered":"Avoid using OFFSET and LIMIT in paginated queries","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Gone are the days when there was no need to worry about optimizing database performance. Time doesn\u2019t stand still. Every new entrepreneur in the tech industry wants to create the next Facebook while striving to collect all the data they can reach. This data is essential for businesses to improve the training of models that help generate revenue. In such conditions, programmers need to create APIs that can efficiently and reliably operate with massive volumes of information.<\/p>\n

\"Avoid<\/a><\/noindex>
\n<\/a><\/noindex>
\nIf you have been designing server-side applications or databases for some time, you have probably written code to execute paginated queries. For example, like this:<\/p>\n

SELECT * FROM table_name LIMIT 10 OFFSET 40\n<\/code><\/pre>\n
\nIs that so?<\/p>\n
But if you have implemented pagination in this way, I regret to inform you that you have done it in a far from optimal manner.<\/p>\n
Do you want to argue with me? You can<\/a><\/noindex> do not<\/a><\/noindex> spend<\/a><\/noindex> time<\/a><\/noindex>. Slack<\/a><\/noindex>, Shopify<\/a><\/noindex> and Mixmax<\/a><\/noindex> are already employing the techniques that I want to discuss today.<\/p>\n
Name at least one backend developer who has never used OFFSET<\/code> and LIMIT<\/code> for executing paginated queries. In MVP (Minimum Viable Product) and in projects where small data volumes are used, this approach is perfectly valid. It 'just works,' so to speak.<\/p>\n
However, if you need to create reliable and efficient systems from scratch, it\u2019s essential to consider the efficiency of database queries used in such systems ahead of time.<\/p>\n
Today we will discuss the issues associated with the widely used (unfortunately) implementations of pagination query mechanisms, and how to achieve high performance when executing such queries.<\/p>\n
What\u2019s wrong with OFFSET and LIMIT?<\/h2>\n
\nAs mentioned earlier, OFFSET<\/code> and LIMIT<\/code> perform excellently in projects that do not need to handle large data volumes.<\/p>\n
The problem arises when the database grows to such sizes that it can no longer fit into the server's memory. Yet during the work with this database, it is necessary to use paginated queries.<\/p>\n
For this issue to occur, a situation must arise in which the DBMS resorts to the inefficient operation of a full table scan when executing each paginated query (while there may also be operations for inserting and deleting data, and we do not need outdated data!).<\/p>\n
What is a 'full table scan' (or 'sequential table scan')? It is an operation in which the DBMS sequentially reads each row of the table, in other words, the data contained within it, and checks them against a given condition. It is known that this type of table scanning is the slowest. The reason is that many input\/output operations are performed, involving the server's disk subsystem. The situation is worsened by delays associated with working with data stored on disks, and the fact that transferring data from disk to memory is a resource-intensive operation.<\/p>\n
For example, you have records of 100,000,000 users, and you execute a query with the structure OFFSET 50000000<\/code>. This means that the DBMS will have to load all these records (which we don\u2019t even need!), place them in memory, and only after that retrieve, say, 20 results as mentioned in LIMIT<\/code>.<\/p>\n
Let's say it might look like this: 'select rows from 50000 to 50020 of 100000'. In other words, for the system to execute the query, it first needs to load 50000 rows. Do you see how much unnecessary work it will have to do?<\/p>\n
If you don't believe it \u2014 take a look at the example I created using the capabilities of db-fiddle.com<\/a><\/noindex>.\u00a0<\/p>\n
\"Avoid
\nExample on db-fiddle.com<\/i><\/p>\n
There, on the left in the field Schema SQL<\/code>, there is code inserting 100,000 rows into the database, and on the right, in the field Query SQL<\/code>, there are two queries displayed. The first, slow one, looks like this:<\/p>\n
SELECT *\nFROM `docs`\nLIMIT 10 OFFSET 85000;\n<\/code><\/pre>\n
\nAnd the second, which is an efficient solution to the same problem, is:<\/p>\n
SELECT *\nFROM `docs`\nWHERE id > 85000\nLIMIT 10;\n<\/code><\/pre>\n
\nTo execute these queries, simply press the button Run<\/code> at the top of the page. By doing this, we can compare the execution time of queries. It turns out that executing an inefficient query takes at least 30 times longer than executing the second one (from run to run, this time varies; for example, the system might report that the first query took 37 ms and the second took 1 ms).<\/p>\n
And if there is more data, it will look even worse (to see this, take a look at my an example<\/a><\/noindex> with 10 million rows).<\/p>\n
What we just discussed should give you some understanding of how database queries are actually processed.<\/p>\n
Keep in mind that the higher the value OFFSET<\/code> the longer the query will take to execute.<\/p>\n
What should be used instead of the OFFSET and LIMIT combination?<\/h2>\n
\nInstead of the combination OFFSET<\/code> and LIMIT<\/code> it is better to use a structure built according to this scheme:<\/p>\n
SELECT * FROM table_name WHERE id > 10 LIMIT 20\n<\/code><\/pre>\n
\nThis is pagination based on a cursor (Cursor based pagination).<\/p>\n
Instead of storing the current ones locally OFFSET<\/code> and LIMIT<\/code> and passing them with each query, you should store the last received primary key (usually, it is ID<\/code>) and LIMIT<\/code>, resulting in queries similar to the one above.<\/p>\n
Why? The reason is that by explicitly specifying the identifier of the last read row, you tell your DBMS where to start looking for the required data. Moreover, the search will be efficient thanks to the use of the key, and the system won\u2019t have to sift through rows outside the specified range.<\/p>\n
Let\u2019s look at the following performance comparison of different queries. Here is an inefficient query.<\/p>\n
\"Avoid
\nSlow query<\/i><\/p>\n
And here is the optimized version of this query.<\/p>\n
\"Avoid
\nFast query<\/i><\/p>\n
Both queries return exactly the same amount of data. However, the first one takes 12.80 seconds to execute, while the second one takes 0.01 seconds. Can you feel the difference?<\/p>\n
Possible issues<\/h2>\n
\nTo ensure the effective operation of the proposed query execution method, the table should have a column (or columns) containing unique, sequentially arranged indexes, like an integer identifier. In some specific cases, this can determine the success of applying such queries to enhance database performance.<\/p>\n
Naturally, when constructing queries, one must take into account the architectural features of the tables and choose the mechanisms that will perform best on the existing tables. For example, if you need to work with large volumes of related data in queries, you might find it interesting this<\/a><\/noindex> the article.<\/p>\n
If we face the issue of a missing primary key, for example, if there is a table with a \"many-to-many\" relationship, then the traditional approach involving the use of OFFSET<\/code> and LIMIT<\/code>, will reliably suit us. However, its application may lead to potentially slow query executions. In such cases, I would recommend using a primary key with auto-increment, even if it is only needed for organizing paginated query executions.<\/p>\n
If you are interested in this topic \u2014 here<\/a><\/noindex>, here<\/a><\/noindex> and here<\/a><\/noindex> \u2014 here are several useful resources.<\/p>\n
Summary<\/h2>\n
\nThe main conclusion we can draw is that, regardless of the size of the databases in question, it is essential to analyze query execution speed. In today's world, scalability of solutions is critically important, and if everything is designed correctly from the outset of a system, it can save developers from many issues in the future.<\/p>\n
How do you analyze and optimize database queries?<\/b><\/p>\n
\"Avoid<\/a><\/noindex>
\n
Source: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u041f\u0440\u043e\u0448\u043b\u0438 \u0442\u0435 \u0434\u043d\u0438, \u043a\u043e\u0433\u0434\u0430 \u043d\u0435 \u043d\u0430\u0434\u043e \u0431\u044b\u043b\u043e \u0431\u0435\u0441\u043f\u043e\u043a\u043e\u0438\u0442\u044c\u0441\u044f \u043e\u0431 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445. \u0412\u0440\u0435\u043c\u044f \u043d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043d\u0430 \u043c\u0435\u0441\u0442\u0435. \u041a\u0430\u0436\u0434\u044b\u0439 \u043d\u043e\u0432\u044b\u0439 \u0431\u0438\u0437\u043d\u0435\u0441\u043c\u0435\u043d \u0438\u0437 \u0441\u0444\u0435\u0440\u044b \u0432\u044b\u0441\u043e\u043a\u0438\u0445 \u0442\u0435\u0445\u043d\u043e\u043b\u043e\u0433\u0438\u0439 \u0445\u043e\u0447\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043e\u0447\u0435\u0440\u0435\u0434\u043d\u043e\u0439 Facebook, \u0441\u0442\u0440\u0435\u043c\u044f\u0441\u044c \u043f\u0440\u0438 \u044d\u0442\u043e\u043c \u0441\u043e\u0431\u0438\u0440\u0430\u0442\u044c \u0432\u0441\u0435 \u0434\u0430\u043d\u043d\u044b\u0435, \u0434\u043e \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043c\u043e\u0436\u0435\u0442 \u0434\u043e\u0442\u044f\u043d\u0443\u0442\u044c\u0441\u044f. \u042d\u0442\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0443\u0436\u043d\u044b \u0431\u0438\u0437\u043d\u0435\u0441\u0443 \u0434\u043b\u044f \u0431\u043e\u043b\u0435\u0435 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0433\u043e \u043e\u0431\u0443\u0447\u0435\u043d\u0438\u044f \u043c\u043e\u0434\u0435\u043b\u0435\u0439, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043f\u043e\u043c\u043e\u0433\u0430\u044e\u0442 \u0437\u0430\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u0442\u044c. \u0412 \u0442\u0430\u043a\u0438\u0445 \u0443\u0441\u043b\u043e\u0432\u0438\u044f\u0445 \u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442\u0430\u043c […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":91049,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-91048","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Avoid using OFFSET and LIMIT in paginated queries | ProHoster","description":"The days are gone when one didn't have to worry about database performance optimization. Time does not stand still.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c\u0441\u044f OFFSET \u0438 LIMIT \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u0445 \u0441 \u0440\u0430\u0437\u0431\u0438\u0435\u043d\u0438\u0435\u043c \u043d\u0430 \u0441\u0442\u0440\u0430\u043d\u0438\u0446\u044b | ProHoster","og:description":"\u041f\u0440\u043e\u0448\u043b\u0438 \u0442\u0435 \u0434\u043d\u0438, \u043a\u043e\u0433\u0434\u0430 \u043d\u0435 \u043d\u0430\u0434\u043e \u0431\u044b\u043b\u043e \u0431\u0435\u0441\u043f\u043e\u043a\u043e\u0438\u0442\u044c\u0441\u044f \u043e\u0431 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445. \u0412\u0440\u0435\u043c\u044f \u043d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043d\u0430 \u043c\u0435\u0441\u0442\u0435.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-08-07T23:42:02+00:00","article:modified_time":"2020-08-07T23:42:02+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"91048","title":null,"description":null,"keywords":null,"keyphrases":{"focus":[],"additional":[]},"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 12:35:24","updated":"2026-08-11 12:50:11","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/91048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=91048"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/91048\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/91049"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=91048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=91048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=91048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}