CODEWITHSUNDEEP
phpmysql
Trevor
Trevor

Reputation: 2457

Long mysql insert queries

Is there a limit to how long these should/can be?

I currently have an insert query

$insert_sql = "INSERT INTO cards (user_id, deck_id,card_one,card_two,card_three,card_order,temp_id) VALUES";

Values

$insert_sql .= "(?,?,?,?,?,?,?)";//int,int,text,text,text,int,int

The texts have a max value of 5,000 characters.

There can be anywhere from 1 to 1,000 of the above insert values

Is it better to break this up into seperate queries?

example:

$insert_sql = "INSERT INTO cards (user_id, 
deck_id,card_one,card_two,card_three,card_order,temp_id) VALUES   
(?,?,?,?,?,?,?), (?,?,?,?,?,?,?), (?,?,?,?,?,?,?), (?,?,?,?,?,?,?)"
This last part here ^ can have between 1-1000 sets of question marks

Upvotes: 3

Views: 243

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521008

From the MySQL documentation:

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns

Assuming you have 8 columns each of which contained 5000 characters, then the smallest amount of memory this would require would be 40KB, assuming only 1 byte per character (and it could more than this depending on the encoding you use). However, you could also exceed the limit imposed by MySQL.

I view your problem as being more of a design issue, specifically you are trying to cram too much data into a single record. You may consider changing your design by giving a single column or subset of columns their own separate table.

With regard to your INSERT statements, there is nothing wrong with doing a bulk INSERT. The only risk in doing 1000 inserts at once, with each record containing a potentially large amount of data, is that you could lock the MySQL database engine, preventing anything else from using it. In this case, you can consider breaking up the INSERT statements into small batches, and spacing out when you insert each batch.

Upvotes: 4

Related Questions