Quick test data creation for performance test in mysql

Question

We want to test the performance of a sequence/bigint vs UUID(VERSION-4) VS UUID(version-7) on mysql database for primary key/index. Being new to mysql and struggling to find an easy way to create data volume easily and test the performance and also 'explain analyze' seems not working here too. Basically want to do something similar to what below code does in postgres.Can you please guide me here?

(UUID Version-4 is the default random UUID version and Version-7 is the timestamp sorted UUID which is more sequential then random so better with regards to caching). Wanted some asy way to generate these in mysql DB itself and test these out.

Below code works fine in postgres and I want to mimic the code similar in mysql to test the performance of UUID vs sequence for generation and INSERT and Querying.

CREATE UNLOGGED TABLE bigint ( id bigint PRIMARY KEY);

CREATE UNLOGGED TABLE uuid7 ( id uuid PRIMARY KEY);

create sequence myseq cache 32767;

Answer 1

(Too many comments for Comments.)

"Caching" -- Be sure test with at least twice as much data as will fit in the innodb_buffer_pool_size. Otherwise, most or all of the data will be cached, and you won't see much difference.

Since "100000" rows may not fill up the default buffer_pool_size, you may want to lower that setting for your testing. (Be sure to include that in the test plan.)

Are the "generate" and "nextval" functions Stored Functions? UDFs? Client code? Please include that code.

LIMIT without ORDER BY is not realistic -- you get some subset of the ids. They will usually, but not necessarily, have consecutive ids.

IN ( SELECT ... ) is a notoriously bad performer. Formulate the test differently so that it is not biased by that problem.

BIGINT -- Almost no application needs more than will fit in a 4-byte INT (versus 8 bytes for BIGINT).

EXPLAIN ANALYZE won't necessarily give you clues about performance. If estimates, sometimes poorly, various things. You must actually time the queries to get useful results.

"timestamp sorted UUID" -- This is relevant only if the access pattern is based on time. An example: A News site will usually be probed for "recent" articles. Be sure to simulate both usage patterns.

SELECT * -- Need details. There are at least 3 unrelated issues that could impact the benchmarking. () Is * so small that the INDEX is "covering"? () Does * include any TEXT or BLOB columns, thereby leading to "off-record" fetches that will be a significant part of the time taken. (*) Other.

UNLOGGED is not a MySQL term.

Use AUTO_INCREMENT, not SEQUENCE. (And several syntax changes.)

For comparing -4 and -7, you need (1) more rows in the table than will fit in the buffer_pool and (2) an access pattern that favors an index that is clustered by time.

MySQL has very little of the functionality you found in elsewhere. About the only thing is BENCHMARK(expression, count), which is probably not useful for your task. Stored Routines can do some of what you need, but they cannot accept or return "arrays". MariaDB has pseudo tables such as seq_1_to_100000.