How to effectively generate large amount of rows

Question

I need to generate large amount of rows in one table, unfortunately, one column needs to be generated on the app side (PHP).

So essentially I need to generate something like this:

 id |  encoded_val_based_on_id
-------------------------------
  1 | my_encode_func( 1 )      
-------------------------------
  2 | my_encode_func( 2 )      
-------------------------------
  3 | my_encode_func( 3 )      
-------------------------------
  4 | my_encode_func( 4 )

I assume, that there must be better way then generating N queries to get N ids and then update these records back with "encoded_val_based_on_id".

Besides that, I'd like to keep the unique index on the "encoded_val_based_on_id", which I'd be unable to do as I'd generate blank rows and then fill them up.

I could lock the table, get the last ID, generate IDs by incrementing in my app + generate all other things, insert them and unlock table. I don't think it's the right way, but at least it can be done in just 1 query (batch insert) and I can keep the unique index there.

Generate it into a plain text file, then use `LOAD DATA INFILE` with indexes turned off. — zerkms, May 30 '13 at 12:12
Just to be sure, writing `my_encode_func()` again as a MySQL stored function is not an option...? — Lukas Eder, May 30 '13 at 12:56
@LukasEder That was actually the first thing I tried to do, but there's a complicated logic behind that function and MySQL is a bit limited in terms of what you can do with custom functions - just plain SQL or C and I don't think that rewriting complicated algorithm from PHP to C is the only way. — Radek Simko, May 30 '13 at 12:59
@zerkms Isn't that just the same idea I wrote at the end of my question? Except there's a difference in storing generated content (file instead memory) - I don't like any of these solutions mostly because IDs are generated outside the database. — Radek Simko, May 30 '13 at 13:00
@Radek Simko: I don't see how it's similar to what you said. Anyway - you asked about an efficient solution - I provided it. — zerkms, May 30 '13 at 20:54

score 1 · Accepted Answer · edited May 23 '17 at 12:05

Until after you have done the insert you do not know the value of id (assuming an auto increment).

Best solution would be to have the function to generate that value as one that can be called from SQL. You could then use a trigger on insert to take the id and calculate the value. However from what you say this is not really feasible.

Both InnoDB and MyISAM allows you to have NULL values within the UNIQUE index.

I think I would be tempted to insert X rows with that field being blank / null. Then retrieve all the rows where that field is null, getting the id fields. Then calculate the values from the id fields and update the rows.

This of course could mean multiple updates which is quite horrible. A solution to that is to do the updates using an INSERT.....ON DUPLICATE KEY UPDATE instead. As you would specify the id you would trigger the update for every row, and as you can have thousands of rows on a single insert query this could be done quite efficiently.

How to effectively generate large amount of rows

1 Answers1