CODEWITHSUNDEEP
mysqlsql
Donnie Darko
Donnie Darko

Reputation: 519

Using CONVERT_TZ causes Perfomance issues?

We are planning if either to change the timezone of the whole database or using CONVERT_TZ in all our functions and events.

Is there any perfomance issues is we decide to use CONVERT_TZ having in mind it could be converting and comparing millions of rows and events every minute?

Upvotes: 2

Views: 1480

Answers (1)

spencer7593
spencer7593

Reputation: 108430

It really depends where you plan to use the CONVERT_TZ function.

For example, if it's wrapped around a column in a WHERE clause, that will disable MySQL ability to use an index range scan operation. And that could have significant performance implications, or cause "performance issues".

For example, if this query is making use an index range scan on an index with mydatetime as the leading column, picking just a few needles out of a big haystack:

 SELECT t.foo
   FROM my_huge_table t
  WHERE t.mydatetime >= '2018-10-09 14:30'
    AND t.mydatetime <  '2018-10-09 15:15'

Adding CONVERT_TZ function around the literals won't cause performance issues. The performance will be the same:

 SELECT t.foo
   FROM my_huge_table t
  WHERE t.mydatetime >= CONVERT_TZ('2018-10-09 08:30','EST5EDT','UTC')
    AND t.mydatetime <  CONVERT_TZ('2018-10-09 09:15','EST5EDT','UTC')

But if we wrap the columns in the WHERE clause in a function, that will force MySQL to evaluate the function and then do the comparison, and it's going to do that for every row in the table.

Do NOT do this:

 SELECT t.foo
   FROM my_huge_table t
  WHERE CONVERT_TZ(t.mydatetime,'UTC','EST5EDT') >= '2018-10-09 08:30'
    AND CONVERT_TZ(t.mydatetime,'UTC','EST5EDT') <  '2018-10-09 09:15'

As long as the SQL still references bare columns in the predicates (conditions in the WHERE clause and ON clause), then adding CONVERT_TZ functions in other places in the SQL shouldn't cause a significant performance impact.

Upvotes: 6

Related Questions