Selecting a specific number of records from a large set is a good idea, but when the set is really large, the effect of degradation of the idea arises. Selecting several records from a certain position creates a real performance drop: before reaching the goal, MySQL looks at other records, spending time on it.
Formally, the MySQL limit can work from the beginning of the table or from its end. A sample can determine a specific number of records and begin at a given position. A case can always arise, that is, the onset of a worse situation is possible. Usually the general flow of customers determines the general statistical mode of operation, but it is necessary to foresee different situations, this is a serious decision in favor of the site.
LIMIT Construction Syntax
In official MySQL sources, syntax limit is indicated as shown in the image below in the context of select and delete queries.
The query for select (select) provides for two numbers: the offset "O" and the number of "R", the request for deletion (delete) is recorded with one number - the number of deleted records "R".
Large values limit "O, R"
MySQL limit: the syntax allows the selection of values according to any scheme. Basic conditions: "O" - offset of the first record selected, "R" - number of records selected. The problem is that if "O" = 9000, then before MySQL selects 9001 records, it will go through the first 9000. If "R" = 1000, then a total of 10,000 records will "take part" in the sample.
MySQL select limit can work from the beginning of the table or from its end, depending on the direction of sorting the asc / desc records. The option of working from the end of the table is not a promising solution, although in some situations it is difficult to do without it.
A design where a large "R" value will be of little interest to the developer and user: MySQL delete limit. And then not in all cases. In this design, the main burden of responsibility rests with the condition of selection (where) of deleted records.
For security reasons and to control the process of deleting records, the developer is usually interested in using the AJAX mechanism and deleting records in small portions. With this mechanism, the site visitor will not notice a delay in the operation of the delete structure.
Fetching one unique record
The correct condition is where and the query "limit 1" MySQL will execute instantly. But to delete or select one record is not always a good solution. Typically, a batch selection of all table entries is used to paginate data (for example, comments, articles, product reviews).
The decision to create the contents of the web page should be made instantly, but with the classic use of MySQL limit O, R, only the first ten of the first hundred records will be quickly selected, then delays will begin.
Meanwhile, not everything is so complicated, you can quickly select one record, but win due to the design and logic of outputting the record to the visitor’s browser.
Nothing prevents us from doing this effectively and hiding the fatal time delays behind the content generation dialog.
Relational Relationships in MySQL
MySQL is a great tool for presenting and processing information. The developer has at his disposal a high-quality dialect of the SQL language and a convenient mechanism for generating queries. Errors and unforeseen situations are logged, data access is administered up to the level of basic operations.
All the flaws relate to the very concept of relational relations. What to do, this concept works so fundamentally and reliably that there is nothing left to do but to reckon with its features and take them into account.
The modern level of hardware development, the high-quality implementation of functionality for all MySQL tools (limit is no exception) ensure the availability of large amounts of data at high speeds and, most importantly, sampling.
Large volumes and standard cache
Buffering data before recording and after sampling is a great idea, leading back to the distant 80s. Caching has become fashionable at all levels of data processing from the processor, network, of course, the level of the http server and the actual database.
The developer can contact the server administrator or independently configure caching at the Apache and MySQL level or another combination of software tools used to ensure the functioning of the web resource and the MySQL server.
This is a normal, standard solution. In most cases, this is customary to do. In programming, the idea of division of labor has long been in demand. The developer makes sites, the administrator manages the work of everything that provides optimization of the use of the site.
In critical situations, when the database tables are large, you have to deviate from the accepted canons. Something needs to be changed in the data organization.
Tabular Organization
Developers are used to it: a relational database is a collection of tables interconnected by keys. Such a simple idea as a table, represented by a mass of pages of the same type with the same name, but with different indexes, goes beyond the usual presentation.
But what is weird here? A table is a set of records containing various data, according to the types of fields (columns, table header). The MySQL query limit query accesses the "big_info" table and selects from the 100,000 position 24 rows for display in the browser.
100024 rows are involved in such a decision in the sample - this is a long time. But if you change the situation and paint the whole table "big_info" into several hundred tables "big_info [0 ... 999]" with 1000 records, then the problem will only occur with the MySQL query "order by * limit O, R", since sorting will be extremely difficult.
However, not only sorting, but also any other operation on all records is impossible by means of the database on the table, which is represented by several tables. There is no index in this context in MySQL.
Relational relationships require clarity: there is a base, it has tables, columns and records in tables. Well, there are still “lotions”: stored procedures, triggers, conditions and other details.
Own cache and the concept of relevance
A good idea from Yandex is the thermal imager: a heatmap of clicks on web pages. This tool shows in a spectral color scheme the spread of relevance of interest of visitors across the "territory" of the page. Apparently, a new school subject will soon appear - the geography of the web page: where and what to place. A good addition to general geography ...
This idea, transferred to the territory of records of a large database table, allows us to formulate an objective thesis: not all the territory of records is in demand and not always.
The larger the flow of visitors, the greater the pattern of sampling needs. MySQL limit is always executed accurately and always for a specific reason. Gathering specific reasons will never be difficult. Binding MySQL limit results in each case is a trivial task for each specific reason.
It turns out not the page organization of the table in the format of hundreds of pages of the same type, but the cone of information demand. Only in fatal cases or when entering the page of an information-intensive visitor, a large amount of data is sampled. In normal mode - crumbs are selected.
Own cache elementary solves the problem of speed: the selection is on the key "specific reason" from a small table of the results of recent operations of the selection from one large table.
Sorts and other wholesale operations
The problem of large amounts of data rests on the performance of the hardware and software. A tremendous level of performance has been achieved today, but data volumes have also increased dramatically.
When the speed and quality of roads grows, the need for rapid movement and instant solution of tasks grows adequately.
The simple operation of sorting, adding a record, or searching for data that directly or indirectly affects all records in a large table is a potential brake, guaranteed performance loss.
Relational relations have held the palm for too long, but they don’t intend to give way to this day: there is simply no one. Other options for organizing data that provide instant navigation over large amounts of information have not been invented even by the super leader of the Big Information industry - Oracle. But Oracle has provided good experience and excellent knowledge in implementing the SQL language and its dialects. This has left a concrete imprint on the MySQL functionality.
The developer can safely use the MySQL limit construct on one data table and have free access to wholesale operations on this large table.
Natural perception of information
A person perceives and processes, for the most part unconsciously, huge volumes of information that are not available to the most advanced tools from Oracle. But he may not be particularly proud of it. Oracle can migrate such volumes of data and perform sortings that require more than one human life and more than one hundred copies.
Everyone should do their own thing and do this thing as efficiently as possible. Relational relations will never die - they are characteristic of the data, it is their integral component. But in the implementation of databases, relational relationships lack semantics. The key organization, indexes for access to records is not the meaning that provides quick access to information.
The sequential organization of machine memory and emulation of associative access to information is the real reason for the loss of time when accessing a large table to select part of the information while observing its integrity for performing group operations.
Information Objects and Natural Associations
The developer cannot escape the sequence of operations. This is how the computer world works. A computer has one processor, and multi-core and multi-processor options are still not a neural organization for parallel processing of information that human thinking uses.
The development of an algorithm always appeals to a single process, albeit split into many threads. Programming is still on the same level, even when the code is built in the format of a system of interacting objects, instances of which function on their own.
The issue is no longer so much in the quality of the structure of information systems in the form of independent objects, but in the environment that ensures their functioning. The environment is consistent, not parallel.
The increase in the number of cores and the number of processors in one computer, tablet or other device does not make them associative computing devices.
But there is still a solution: each specific application is a question that needs to be answered quickly. You need to make a quick choice (MySQL limit) despite the fact that the rest of the functionality (MySQL order by, group by, join & where) will not suffer, the table will not be divided into many parts of the same type, and updated data will fall into the caching procedures immediately after updating them , and not when the next "specific reason" comes to them.
SQL is a good language, but if you add associations to it, it will become even better.