Your question, "How MySQL select request works internally" is a very broad topic. However, I can more or less give a not-completely-accurate, but simple overview to "what happens when you select records 1 to 10 and then, sequentially, 1-15" regarding caching.
First thing you must understand is that, functionally, MySQL can be divided into 2 separate layer, the "frontend" or SQL layer, and the ENGINE layer, which communicate just by a low-level (remember I am simplifying things) row-based api. You can have InnoDB or other pluggable engines to store rows (let's focus on only that one). Caching can happen at SQL or Engine level:
At SQL layer there are several caching mechanisms, let's focus on query results caching, known as Query Cache. It allows to allocate certain amount of space in memory to return query results without going to the engine. It is a very high level method of caching, has to be consistent with the actual data stored, and its implementation is very simplistic, which has lead to many practical problems:
- It requires a global lock for writes, which kills all parallelism and creates high contention under medium to high load, even for low number of writes
- It can slow down misses
- Evictions can also be harmful, and all results are invalidated as soon as a table is written, even if results would not be affected
- If the same results are returned, but the query is slightly different, those are considered as 2 different queries, storing the same queries twice
- It does not support partitioned tables, and clustering solutions such as Galera do not support it
- Not all queries are cachable (e.g. undeterministic queries), and there will be a finite number of space for them
In your case, having executed 2 different queries, the query cache would be useless, and you would have 2 misses, following by 2 separate inserts of the results (assuming they are cachable, etc.)
Many people recommend disabling the query cache and using the extra memory for other buffers. The exact impact would depend on your particular workload (you should monitor hits/misses/evictions and query latency). If you still need query caching, you can opt to have something like memcache, closer to the application layer. And even MySQL integrates a plugin to run memcache as a frontend for InnoDB, as part of the server.
Separating the caching, you can control yourself the TTL of the cached results. There are many other mechanisms that can handle that, such as ProxySQL or implementing your own on the application.
InnoDB implements it's own complex caching system for faster row retrieval inside the buffer pool (its page cache), a much lower-level cache. In general, InnoDB tries to avoid (overpass) the filesystem cache and implement its own row cache system. The default page size is 16KB (although that can be changed) and it has the same structure on disk than in memory. A page should be large enough to store 2 data rows or more (a row can be larger than 8K, for variable-size fields, but that is another story). When a page is accessed on disk, it gets copied to the buffer pool. In fact, due to some optimizations, in many cases, not only a single page gets read, but multiple contiguous ones to reduce IO operations.
So when you read the first 10 rows on an empty buffer pool, maybe only those pages are are loaded into memory with the rows you want and probably some more; and in many cases, the full extend and the next one. All of this is subject to tuning and depends on the kind of access patterns you have.
When you read again, you will probably find the same rows already in memory, and thus you have reduced the disk I/O. If you are missing some pages, it will require a (or several) disk IO operation. This is normally seen when you run the same query twice and get a faster response. Although row caching is only a part of the speed up- you also have thread caching, table caching, dictionary caching, index caching ...(at both SQL and engine level).
However, it can happen that by the time you have done the second query, the rows have been evicted- the eviction follow a relatively complex algorithm and it is also highly tunable, but it is -simplifying it- a LRU (Least recently used) implementation, which means that if the other, most recently accessed data gets into memory, it can evict rarely-used data.
Here you have a step-by-step summary of how InnoDB buffer pool works:
Other engines can do things differently, for example, MyISAM and TokuDB have both index caching, but they both relay more heavily on the filesystem cache for data.