Some ideas on optimizing mbtiles file storage size with a single 32-bit index instead of z/x/y

optimized-mbtiles.sql

create table map
(
    tile_index  INTEGER,
    tmp_zoom    INTEGER GENERATED ALWAYS AS ((tile_index & 0xFC000000) >> 26) VIRTUAL,
    tile_column INTEGER GENERATED ALWAYS AS (CASE
                                                 WHEN tmp_zoom <= 13 THEN (tile_index & 0x3FFE000) >> 13
                                                 ELSE (tile_index & 0x3FFF8000) >> 15 END) VIRTUAL,
    tile_row    INTEGER GENERATED ALWAYS AS (CASE
                                                 WHEN tmp_zoom <= 13 THEN tile_index & 0x1FFF
                                                 ELSE tile_index & 0x7FFF END) VIRTUAL,
    zoom_level  INTEGER GENERATED ALWAYS AS (CASE
                                                 WHEN tmp_zoom <= 13 THEN tmp_zoom
                                                 ELSE ((tmp_zoom & 0x30) >> 4) + 12 END) VIRTUAL,
    tile_id     TEXT
);

create unique index map_index
    on map (tile_index);

-- This is needed only for the systems that don't support single-value tile indexing
-- It will use up some space, possibly making it more than the original (needs testing)
-- create unique index map_index2
--     on map (zoom_level, tile_column, tile_row);

create table images
(
    tile_data blob,
    tile_id   text
);

create unique index images_id
    on images (tile_id);

CREATE VIEW tiles AS
SELECT map.zoom_level  AS zoom_level,
       map.tile_column AS tile_column,
       map.tile_row    AS tile_row,
       images.tile_data AS tile_data
FROM map
       JOIN images ON images.tile_id = map.tile_id;

--
-- Copy data from the existing mbtiles file
--
ATTACH DATABASE 'original_db' AS src;

INSERT INTO map 
SELECT (CASE
            WHEN zoom_level <= 13 THEN
                (zoom_level << 26) | (tile_column << 13) | tile_row
            ELSE
                ((zoom_level-12) << 30) | (tile_column << 15) | tile_row
           END) AS tile_index,
    tile_id
FROM src.map;

INSERT INTO images
SELECT tile_data, tile_id FROM src.images;