CODEWITHSUNDEEP
mysqlsqldatabase
Eric
Eric

Reputation: 3363

Efficiency of subqueries vs running functions several times?

I have the following two queries and I wonder what's more efficient to do: a) to use a subquery and an inner join or b) to call CONVERT_TZ() several times?

Or maybe there's a more efficient way. Any ideas will be appreciated!

What the queries do is convert a DATETIME from one timezone to another (EDT:GMT-4 in this case) using the offset value already stored. I will have a few thousands of records when running this query in production.

USE weblist;

SELECT metropolitan_area 
  ,date_time
  ,gmt_offset
  ,gmt_offset + 4 AS hours
  ,CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') AS EDT
  ,DAYOFMONTH(date_time) AS day
  ,MONTH(date_time) AS month
  ,DATE_FORMAT(date_time, '%h:%i %p') AS date_time_
  ,DAYOFMONTH(CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00')) AS dayEDT
  ,MONTH(CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00')) AS monthEDT
  ,DATE_FORMAT(CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') 
  ,'%h:%i %p') AS date_timeEDT
  FROM weblist
  ORDER BY gmt_offset DESC;

SELECT cl.metropolitan_area
  ,cl.date_time
  ,edt.date_timeEDT
  ,DAYOFMONTH(cl.date_time) dayOrg
  ,MONTH(cl.date_time) AS monthOrg
  ,TIME_FORMAT(cl.date_time, '%h:%i %p') AS dateTimeOrg
  ,DAYOFMONTH(edt.date_timeEDT) dayEDT
  ,MONTH(edt.date_timeEDT) AS monthEDT
  ,TIME_FORMAT(edt.date_timeEDT, '%h:%i %p') AS dateTimeEDT
  FROM (
    SELECT 
      id
      ,CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') AS date_timeEDT
    FROM weblist
    ) edt
  INNER JOIN weblist AS cl ON cl.id = edt.id
  ORDER BY cl.gmt_offset DESC;

Upvotes: 0

Views: 195

Answers (2)

Scott Bruns
Scott Bruns

Reputation: 1981

If you are wondering which is more efficient, decided which metrics you would use to measure efficiency and then measure them. Anything else is just guessing.

Upvotes: 2

Alain Collins
Alain Collins

Reputation: 16362

I'd vote for the multiple CONVERTs. I think I've read that mysql is smart enough to notice that they're all the same, so it will only do the math once for each row.

Also, think about what happens when you want to add a WHERE clause. Are you always going to remember to add it to the inner and outer SELECT statements in weblist?

Upvotes: 0

Related Questions