0

Before doing some update I want to insert original table data for log purpose. For that below which query performs well without any issues?

query1

INSERT INTO
   log_table 
   SELECT
      * 
   FROM
      details_table 
   WHERE
      id = 12 
      AND asn = 765487

query 2

INSERT INTO
   log_table (col1, col2, col3, col4) 
   SELECT
      col1,
      col2,
      col3,
      col4 
   FROM
      details_table 
   WHERE
      id = 12 
      AND asn = 765487

Doing this with php. Any suggestion would be greatly appreciated

Philipp
  • 2,787
  • 2
  • 25
  • 27
user3408779
  • 981
  • 2
  • 19
  • 43
  • 4
    Assuming that the columns retived by * and (col1, col2, col3, col4) are the same the query are the same also for performance . – ScaisEdge Feb 05 '19 at 07:05
  • Query 1 as it doesn't specify the columns to insert into and the columns to fetch the data from is potentially a disaster waiting to happen. If there are any column mismatches or 1 table gets modified and the other doesn't it will fail. There are already columns `id` and `asn` which it will attempt to insert with potentially no target column. – Nigel Ren Feb 05 '19 at 07:23
  • 1
    **mario** clearly didn't read the question before marking it as duplicate. – Styx Feb 05 '19 at 08:45
  • @mario , Please read the question once again and please remove duplicate .. thanks – user3408779 Feb 05 '19 at 11:22
  • 1
    @mario OP didn't ask about profiling SQL queries, not about enabling query log, so those questions you've added as duplicates won't help him/her at all. The question itself is about a choice of selecting/inserting all columns vs selecting/inserting particular columns. It could be marked as "primarily opinion based" though, but definitely not as duplicate. – Styx Feb 05 '19 at 11:45
  • @Styx, exactly that is what I want to know. Thank you – user3408779 Feb 05 '19 at 11:52
  • @Styx I would feel this is mostly off-top for the lack of table definition. Though I have a hard time imagining the query plan diverting much if at all. That's what the query/profiler logs/`explain` would reveal though. // Anyway, the dupes seem relevant for future users going by the coarse question title though. – mario Feb 05 '19 at 11:53
  • 1
    @mario Oh, I completely missed the "which is faster" in title. I was about to ask OP to clarify "well without any issues", but noticed that he/she indeed wants to know which one is _faster_. In the light of that your marking as duplicate is completely reasonable. – Styx Feb 05 '19 at 11:57

0 Answers0