CODEWITHSUNDEEP
sqlmysql
Sabya
Sabya

Reputation: 11894

Reusing computed columns in mysql

How can I reuse a computed column in SQL in MySQL? Say: my query is something like: -

SELECT 
    CONVERT_TZ(
        if(timestamp_start > last_update, timestamp_start, last_update), 
        'GMT', 
        user.timezone
    ) as time_usr_tz
from 
    shecdule
    inner join user on shecdule.user_id = user.user_id
where 
    CONVERT_TZ(
        if(timestamp_start > last_update, timestamp_start, last_update), 
        'GMT', 
        user.timezone
    )
    < CURRENT_TIMESTAMP();

If you see the query the "CONVERT_TZ....." part is repeated.

This is only a sample query. Actually, I have to use that computed column several times. So if I make change in one place, I have to change in many places. And the size of the query becomes scary, too.

Is there any way to avoid such duplication?

UPDATE I already have sub-query in my original query, so sub-query is not a preferable option. Is it possible in any other way?

Upvotes: 1

Views: 1994

Answers (1)

Quassnoi
Quassnoi

Reputation: 425401

SELECT  ctz
FROM    (
        SELECT  shecdule.*,
                CONVERT_TZ(
                if(timestamp_start > last_update, timestamp_start, last_update), 
                'GMT', 
                user.timezone
                ) AS ctz
        FROM    shecdule
        INNER JOIN
                user
        ON      user.user_id = s.user_id
        ) s
WHERE  ctz < CURRENT_TIMESTAMP()

Note that this query doesn't use the indexes on timestamp_start and last_update efficiently.

See the performance details in the article in my blog:

In two words, you better use this:

SELECT  ctz
FROM    (
        SELECT  shecdule.*,
                CONVERT_TZ(
                if(timestamp_start > last_update, timestamp_start, last_update), 
                'GMT', 
                user.timezone
                ) AS ctz
        FROM    shecdule
        INNER JOIN
                user
        ON      user.user_id = s.user_id
        WHERE   timestamp_start < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
                OR last_update < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
        ) s
WHERE  ctz < CURRENT_TIMESTAMP()

This inner query will perform coarse filtering on timestamp_start and last_update using indexes (no zone conversion can offset the time more than +14:00 hours from UTC).

The outer subquery will then fine filter the results based on user's time zone.

If you don't like subqueries, you may also use:

SELECT  shecdule.*,
        CONVERT_TZ(
        if(timestamp_start > last_update, timestamp_start, last_update), 
        'GMT', 
        user.timezone
        ) AS ctz
FROM    shecdule
INNER JOIN
       user
ON      user.user_id = s.user_id
/* 
        WHERE   timestamp_start < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
                OR last_update < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
*/
HAVING  ctz < CURRENT_TIMESTAMP()

Upvotes: 4

Related Questions