How can you generate a fixed number of rows in MySQL v8 for performance testing?

Question

I am trying to create performance tests in MySQL v8. For this, I need to generate a fixed number of rows, so I can insert them into my tables.

In PostgreSQL, I would do something like:

insert into film(title)
select random_string(30)
from   generate_series(1, 100000);

Here, random_string(int) is a custom function. In MySQL, I could use something as mentioned in https://stackoverflow.com/a/47884557/9740433, which would be sufficient I suppose.

How do I generate the 100k rows in MySQL v8?

you can create a procedure where you can iterate data using while and insert that data into temporary table .after that you can access that temporary table for your testing purpose. — Ankit Agrawal, May 28 '18 at 07:59
You're using 8, so it's easy. The manual even provides an example just like this — Strawberry, May 28 '18 at 08:12

Jelly Orns · Answer 1 · 2018-05-28T08:45:41.320

0

To answer my own question, as per comment by Strawberry:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

You might want to alter the recursion depth: SET SESSION cte_max_recursion_depth = 1000000;.

edited May 28 '18 at 08:45

answered May 28 '18 at 08:29

Jelly Orns

197
1
2
8

score 0 · Answer 2 · answered May 28 '18 at 12:40

You can use the old-fashioned cross join method:

with d as (
      select 0 as d union all select 1 union all select 2 union all select 3 union all
             select 4 union all select 5 union all select 6 union all
             select 7 union all select 8 union all select 9
     ),
     n as (
      select (1 + d1 + d2 * 10 + d3 * 100 + d4 * 1000 + d5 * 10000) as n
      from d d1 cross join
           d d2 cross join
           d d3 cross join
           d d4 cross join
           d d5
     )
select *
from n;

It would be interesting to test the performance of the cross join against the recursive CTE.

How can you generate a fixed number of rows in MySQL v8 for performance testing?

2 Answers2