Hello. I had a mysterious issue where dynmap would not start the surface render after completing the flat render. Discord informed me it was likely still doing zoomout renders, but this did not resolve after over a day. This issue did not occur in file mode - only after switching to SQL mode and attempting to do a full render.

Upon further inspection, the following query was hanging the database

SELECT x,y,zoom,Format FROM Tiles WHERE MapID=1 LIMIT 100 OFFSET 120000

This takes quite a long time (over 5 minutes) because of the extremely high offset. If you try the same query without the offset or with a low offset, it is near instantaneous. This is because each row is not the same size, therefore the database engine must go through every single row until it has reached the offset. Since these rows happen to contain blob data this takes an especially long time. In essence, before it can get the 100 images it actually needs it has to first plow its way through 120 thousand (or whatever the offset happens to be at the time) other images. It must keep doing this repeatedly, increasing the offset each time, until it has completed the zoomout render procedure.

Perhaps instead each tile could have a sequential ID, and it could use that instead of OFFSET?
Example:

SELECT x,y,zoom,Format FROM tiles
WHERE MapID=1
AND Id > 120000
ORDER BY Id
LIMIT 100

This should solve the problem because the database indexes the PK and therefore already knows where Id 120000 is. This idea is confirmed by this stack overflow thread https://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down

this is kinda a show stopper for large servers because its impractical to have millions of tiny files in a file tree and this stops it from getting past the flat render in SQL mode on a large world

Cannot use ID (I'm assuming you mean some sort of autoincrement column, which this table doesn't actually have...), since the table is a mixed set of rows, and the Id index would not necessary be ordered nor packed relative to the MapID. The call is already consistent with the primary key (which is already PRIMARY KEY(MapID, x, y, zoom), so seeking to the start of the ordered range represented by WHERE MapID=X is trivial, and this table does not have an ID column.... I can say that I personally use this on a server with millions of tiles on multiple maps, with MySQL, and do not find this a problem. Also, adding an 'order by' would be quite bad, since it would force the result set matched by the WHERE to need to be sorted first before the offset/limit could be applied, which is really the problem indicated by the StackOverflow post.

Feel free to code whatever solution you see fit, but I can confirm that I do not see this query as slow on my MySQL server.

I was pretty sure ordering by an index does not take meaningfully longer so I tested it myself. The following query (no sorting) took 0.031 seconds and 22 seconds to fetch

USE dynmap;
SELECT x,y,zoom,Format FROM Tiles 
WHERE MapID=1
LIMIT 10000

The following query (sorted by your composite key, same limit) took 0.015 seconds and 27 seconds to fetch

USE dynmap;
SELECT x,y,zoom,Format FROM Tiles 
WHERE MapID=1
ORDER BY
(   SELECT `COLUMN_NAME`
    FROM `information_schema`.`COLUMNS`
    WHERE (`TABLE_SCHEMA` = 'dbName')
      AND (`TABLE_NAME` = 'tableName')
      AND (`COLUMN_KEY` = 'PRI')
);

I don't really see why it matters if this hypothetical ID column was packed with the MapID, because any tiles from the wrong MapID would just not be included in the result. For example, say from 1-100 there are 80 results with the Map ID you are looking for and from 1-120 there are 100. If you limit the results to 100, it would simply return all the results with the correct map ID 1-120, and then you would continue from ID 120.

However if I am missing something and this is a problem, the Id could just be an auto increment column unique to each map ID rather then the entire table and you could index by a composite of the map ID and ID.

I will implement the latter solution just in case

If it is taking you 27 seconds to return 10000 simple rows (just a couple of number columns) from your MySQL, I think you've got something else going on... your query time is reasonable, but the notion that the fetch (I/O) would take that long is very off... unless you're measuring the console I/O time (scrolling), which is meaningless to include.

That aside, the original claim is that 'SELECT x,y,zoom,Format FROM Tiles WHERE MapID=1 LIMIT 100 OFFSET 120000' is taking over 5 minutes - there is no way this would be the case, even if the table were unindexed: a scan of 120000 rows might take seconds, but never minutes, and the fact that the condition is based on the first element of the primary key means that the scan would be an index scan, not a table scan, so the size of the blobs has zero to do with it (as the blobs are not part of the index that is the primary key).
You might want to try looking at the execution plan you get when trying that query (as in using EXPLAIN).

I was measuring the fetch time via workbench which presumably includes the time it took to send it over the internet as I am not on the same network as the server, however I see where you are coming from. I will try switching from Maria to MySQL and failing that I'll closely review the execution plan

Yeah - your second example also isn't relevant (USE dynmap;
SELECT x,y,zoom,Format FROM Tiles
WHERE MapID=1
ORDER BY
( SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE (TABLE_SCHEMA = 'dbName')
AND (TABLE_NAME = 'tableName')
AND (COLUMN_KEY = 'PRI')
);) as you are adding the sort by using the primary key in the primary order of that key - which means that the ORDER BY is null (since the primary key is already being used, and its 'natural' order is consistent with those columns - this would not be the case if the columns in the order by were NOT those of the primary key or index being used).

The key thing to understand about the original query is its use:

SELECT x,y,zoom,Format FROM Tiles WHERE MapID=1 LIMIT 100 OFFSET 120000

This is part of a post-render traversal of the map tiles to see if there exists tiles that were NOT part of the full render, so that those junk (no longer part of the render - usually because of map trimming or resizing) can be removed: if you saw this query, it's because 1200 of them had already been done (SELECT x,y,zoom,Format FROM Tiles WHERE MapID=1 LIMIT 100 OFFSET 0, SELECT x,y,zoom,Format FROM Tiles WHERE MapID=1 LIMIT 100 OFFSET 100, SELECT x,y,zoom,Format FROM Tiles WHERE MapID=1 LIMIT 100 OFFSET 200, etc) - so unless this had been running for about 16 hours, that individual query isn't taking 5 minutes. If the map is big, this total process might take some time, but the smaller queries are specifically used to avoid timeouts and table lock issues - but, since its just the end of a full render that likely took hours, who cares if it take a couple minutes total to find and remove dead tiles: the individual queries should be taking a couple hundred milliseconds, tops.