-1

Uploading multiple lines to a database using MySQLi, what is the maximum best practice amount of lines to upload in a singular instance?

Tim Marshall
  • 360
  • 5
  • 25

1 Answers1

1

I think a simple answer, for most scenarios, is that as long as your query length doesn't exceed your mysql max_allowed_packet setting, the specific number of rows probably doesn't matter a whole lot.

If you think about your query, take into account the max field length of each field you're inserting into, the general formula would be something like

max_rows = floor((max_allowed_packet-base_query_length)/characters_per_row)

Where base_query_length is the number of characters required for the start part of the query (e.g. INSERT into xxx (field1,field2,field3) VALUES)

And characters_per_row is max length for each row based on the theoretical max values for each field. If your database has been planned well, and your input has been validated to support these limits, you should be able to work this out based on the length limits in your database. Note you would need to also take into account space for the leading (, trailing ), separating , between row data sets, quotes around string values, etc.

Note I haven't done any research to support this answer - it just seemed to make sense in my mind. If anyone can dis-prove my assumptions or provide corrections for any mistakes, please do so in the comments or edit my answer.

Joel Cox
  • 3,289
  • 1
  • 14
  • 31
  • could you please explain what `max_allowed_packet` means? I am new to PHP and MySQL. I am presuming this checks if the upload does not exceed the the storage allowed on my database? – Tim Marshall Feb 09 '15 at 07:01
  • Effectively that variable indicates the maximum query length myself will accept. See this question/answer for more info: http://stackoverflow.com/q/16335011/3180129 – Joel Cox Feb 09 '15 at 07:06
  • Presumably you mean "MySQL" not "Myself" :P – Tim Marshall Feb 09 '15 at 07:11
  • So I would do the following? -> `SHOW VARIABLES TABLE_NAME 'max_allowed_packet';` to see my maximum allowed packet? – Tim Marshall Feb 09 '15 at 07:13
  • Yep that's right. Also yes I ment "mysql". Sorry - typing on phone. Will edit the comment later to fix the typo. (don't seem to be able to from the stack exchange mobile app) – Joel Cox Feb 09 '15 at 07:38
  • In theory, if 'max_allowed_packet' equalled to '100' could I upload 100 lines and then another straight away or is a delay needed between? – Tim Marshall Feb 09 '15 at 07:47
  • 1
    Not exactly. It's the maximum number of characters you can have in a query. I'll edit my answer in a few hours with some specific examples :-) – Joel Cox Feb 09 '15 at 08:10