CODEWITHSUNDEEP
sqlduplicatesmariadb
Bensge
Bensge

Reputation: 143

How to avoid duplicated SELECT phrases in SQL (MariaDB)

I am working with a small MariaDB database. To extract time intervals per user, I use the following query:

SELECT
  SUM(TIMESTAMPDIFF(SECOND,Activity.startTime,Activity.endTime)) AS seconds,
  TIME_FORMAT(SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,Activity.startTime,Activity.endTime))),'%Hh %im %ss') AS formattedTime,
  TSUser.name
FROM Activity
INNER JOIN User ON User.id = Activity.userID
GROUP BY User.id
ORDER BY seconds DESC;

I have to select the time as plain seconds (... AS seconds) to be able to order the results by it, as can be seen in my query.

However, I also want MariaDB to format the time interval, for that I use the TIME_FORMAT function. The problem is, I have to duplicate the whole SUM(...) phrase inside the TIME_FORMAT call again. This doesn't seem very elegant. Will MariaDB recognize the duplication and calculate the SUM only once? Also, is there a way to get the same result without duplicating the SUM?

I figured this should be possible with a nested query construct like so:

SELECT
  innerQuery.name,
  innerQuery.seconds,
  TIME_FORMAT(SEC_TO_TIME(innerQuery.seconds), '%Hh %im')
FROM (
  //Do the sum here, once.
) AS innerQuery
ORDER BY innerQuery.seconds DESC;

Is this the best way to do it / "ok" to do?

Note: I don't need the raw seconds in the result, only the formatted time is needed.

I'd appreciate help, thanks.

Upvotes: 1

Views: 285

Answers (2)

Rick James
Rick James

Reputation: 142278

In this case, you don't need the raw values. The formatted value will work correctly in the ORDER BY.

Your subquery idea is likely to be slower because of all the overhead in having two queries.

This is a Rule of Thumb: It takes far more effort for MySQL to fetch a row than to evaluate expressions in the row. With that rule, duplicate expressions are not a burden.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Alas. There isn't a really good solution. When you use a subquery, then MariaDb materializes the subquery (as does MySQL). Your query is rather complex, so there is a lot of I/O happening anyway, so the additional materialization may not be important.

Repeating the expression is really more an issue of aesthetics than performance. The expression will be re-executed multiple times. But, the real expense of doing aggregations is the file sort for the group by (or whatever method is used). Doing the sum() twice is not a big deal (unless you are calling a really expensive function as well as the aggregation function).

Other database engines do not automatically materialize subqueries, so using a subquery in other databases is usually the recommended approach. In MariaDB/MySQL, I would guess that repeating the expression is more efficient, although you can try both on your data and report back.

Upvotes: 1

Related Questions