offset_testcase.py

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="127.0.0.1", 
        port="4000",
        user="root",   
        password="",
        database="test" 
    )

    if connection.is_connected():
        connection.autocommit = True
        print("Connected to MySQL")

    cursor = connection.cursor()

    cursor.execute('drop table if exists d')

    cursor.execute('''
CREATE TABLE d (
mid bigint(20) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
ov longblob DEFAULT NULL,
version int(11) unsigned NOT NULL,
PRIMARY KEY (object_id)
)
'''
)
    
    cursor.execute('drop table if exists i')

    cursor.execute('''
CREATE TABLE i (
object_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
lid bigint(20) DEFAULT NULL,
sid varbinary(767) DEFAULT NULL,
cid bigint(20) DEFAULT NULL,
version int(11) unsigned NOT NULL,
PRIMARY KEY (object_id) /*T![clustered_index] CLUSTERED */,
KEY i_1 (cid,sid,object_id),
KEY i_2 (lid,object_id),
KEY i_3 (cid,object_id)
)                   
''')

    for i in range(30000):
        cursor.execute(f'insert into i(lid,sid,cid,version) values({i},bin({i}),249,{i})')
        cursor.execute(f'insert into d(mid,ov,version) values({i},bin({i}),{i})')
        if i % 1000 == 0:
            connection.commit
            print (i)


except mysql.connector.Error as err:
    print(f"Error: {err}")

 '''
 explain SELECT
  `d`.*
FROM
  `i`
  LEFT JOIN `d` ON `i`.`object_id` = `d`.`object_id`
WHERE
  `i`.`cid` = 249
ORDER BY
  `i`.`object_id`
LIMIT 1000 OFFSET 18000;
'''