SQL: insert as 1 query with all fields, or multiple queries?

Question

So I have a question regarding the best practice for a PHP page inserting a new row into a table. There are some required fields (aka NOT NULL) and some optional fields (can be NULL). Is it better to insert everything with one query, or just do the required fields, get the inserted ID and update the other fields with one or more other queries?

Is there a big performance hit if I do multiple updates with a where <primary key> = "x"? Or is it better to do multiple updates so that if one of the optional updates doesn't work, the rest do?

Answer 1

In general you should do a single INSERT with all the values. This will be less expensive in terms of database processing but equally importantly (or moreso) it will make your application code much simpler.

I'm not sure why the alternative even occurred to you, but it makes me think that perhaps there's some application specific reason you feel some of the subsidiary column values will be rejected. If that's so, please tell use more about your specific situation.

Answer 2

I would use one query for two reasons:

1. Each call to the database is going to add overhead to your application. If you can get away with only doing one update that do it that way.
2. If you use separate calls then there might be the possibility that someone tries to view the data in between updates and sees 'incomplete' data.

Answer 3

For a single row, you should really be doing a single INSERT. If there is concern about a column value not being valid, more emphasis should be placed in the application layer to validate the code (data type, data length, etc) before making the INSERT.

You'll have more thorough code this way and will make much less of an impact on the application server, database server, and network at the same time.