CODEWITHSUNDEEP
phpmysqlperformance
santiago arizti
santiago arizti

Reputation: 4767

MySql performance, FROM_UNIXTIME vs Parse in PHP to date string

There are similar questions to this one in SO bot not quite what I want to know.

I have an insert statement that looks like this:

insert into my_table (id, time) values
(1, from_unixtime(1495488539)),
(2, from_unixtime(1495488539)),
...
(99, from_unixtime(1495488539)),
(100, from_unixtime(1495488539));

That timestamp was obtained with php with like this: $time = time().

It looks to me like that function will be executed for every row in the insert, and it sounds inefficient.

My other option is to generate the time like this in php: $time = date('Y-m-d H:i:s') and my insert statements will look like this:

insert into my_table (id, time) values
(1, '2017-05-22 21:28:59'),
(2, '2017-05-22 21:28:59'),
...
(99, '2017-05-22 21:28:59'),
(100, '2017-05-22 21:28:59');

That one looks simpler because there are no function calls on each row, but MySQL still parses each time the string, right?

The question is which of the two flavors should I use to have better performance?

Why I don't simply use current_timestamp or now()? because the date must be the same for all rows, and I don't do a single insert, I spread it in many different smaller insert statements, so in reality my php looks more like $time = $global_time_same_for_all_rows_not_exactly_now. In other words, that time is treated kind of like an import-key of some sort.

Upvotes: 1

Views: 269

Answers (2)

Rick James
Rick James

Reputation: 142560

This one is probably optimized:

insert into my_table (id, time) values
    (1, NOW()),
    (2, NOW()), ...;

That is because certain functions are deliberately evaluated once before a statement -- NOW, UNIX_TIMESTAMP, UUID?, and a few others, but not RAND.

But, all of this is mostly irrelevant. The overhead of performing the row inserts (or selects, etc) far exceeds the trivial time it takes to perform from_unixtime(1495488539), or virtually any other simple function. It's possibly the difference between milliseconds versus sub-microsecond timing.

Upvotes: 1

entity53
entity53

Reputation: 21

Assigning date() to $time, and then using that variable repeatedly in multiple insert statements will be less overhead.

Using time() in php and then using from_unixtime in mysql to convert back to a datetime is an additional unnecessary step.

Upvotes: 1

Related Questions