Clever Geek Handbook

Will use UNIX_TIMESTAMP () to slow down my INSERT query

I need to do more than 20 insert statements in one go. I am using UNIX_TIMESTAMP()

there to insert seconds from epoch into my time columns.

My php timezone UTC

So what should I use $time = time()

to insert values ​​or UNIX_TIMESTAMP()

ok.

+3


source to share


3 answers


time()

time - the current Unix timestamp

UNIX_TIMESTAMP ()



If called with no argument, returns the Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer.

It looks like they are the same thing. So I highly doubt there would be a noticeable enough difference to justify choosing one over the other just for optimization. Choose what makes your code easier to read and understand will be my suggestion. MySQL can handle a very large number of actions at once, so doing something as trivial as getting a timestamp will be almost instantaneous.

+1


source


Whatever you do, decide on functionality, not performance ; you probably won't even feel the difference, even with 100 inserts.

Which choice should depend on your functional requirements:

  • If you are absolutely sure that the value for each statement is the same, you should "cache" the value time()

    and use it for insertion; btw, this can be done in either MySQL or PHP (see below).

  • For everyone else, choose what you like.



If you are inserting multiple records, I hope you have reviewed prepared statements; this can actually speed up insert statements in a more significant way, mainly due to less data that needs to be moved between the code and the database. For example:

SET @time = UNIX_TIMESTAMP();
PREPARE stmt FROM 'INSERT INTO mytable VALUES (@time)';
EXECUTE stmt;
EXECUTE stmt;
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
+1


source


If you are going to insert a billion rows and it will take you a whole day to insert it and you really really want the timestamp to be an issue, ask who has the data if it is okay if you are inserting all rows with the same timestamp. Then use one generation of the timestamp, put it in a variable, and use it for all insert statements as value. This way you run less code for each insert. But this applies to those who own the data, if they are happy with it :)

0


source






More articles:


All Articles