mysql performance comparison

Question

Which is more efficient,and by how much?

type 1:

insert into table_name(column1,column2..) select column1,column2 ... from another_table where 

columnX in (value_list)

type 2:

insert into table_name(column1,column2..) values (column1_0,column2_0..),(column1_1,column2_1..)

The first edition looks short,and the second may become extremely long,when value_list contains,say 500 or even more values.

But I've no idea about whose performance will be better,though feels the first should be more efficient,intuitively.

Answer 1

The first is cleaner, especially if your columns are already in mysql (which I'm assuming you are saying?). You would save some time in network overhead sending data, and parsing time, and have to worry less about hitting whatever query size limit your client has.

However, in general, I would expect the performance to be similar as the number rows grows larger, especially on a well-indexed table. Most of the time for inserts w/ large queries is spent doing things like building indexes (see here), and both those queries, absent turning indexes off, would have to do that.

Answer 2

I agree with Todd, the first query is cleaner and will be faster to send to the MySQL server and faster to compile. And it's probably true that as the number of inserted records increases, the speed differential will drop.

But the first form has substantial other benefits to consider:

• It's far easier to maintain: you only have to add or modify a field every now and then.
• You avoid the expense of querying another_table and processing the results to concatenate the second query (a hidden cost of that approach).
• If you need to run this update more than once, the first query can be cached in the MySQL server along with its compiled form and query plan. This makes subsequent invocations of the query run a bit faster.