0

Using MySQL, I return the age in years and months as separate fields in a query.

This may well be a premature optimization but the duplicate calls to datediff and from_days in this query are upsetting.

Is it possible to get the from_days(datediff(now(), user_birthdate)) factored into a single call without a nested query?

SELECT 
    user_id,
    year(from_days(datediff(now(), user_birthdate))) as years,
    month(from_days(datediff(now(), user_birthdate))) as months

or... would a nested query be a win over this current query?

Like this:

SELECT 
    user_id,
    YEAR(age) AS years,
    MONTH(age) AS months
FROM
    (SELECT 
        *, FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(user_birthdate)) AS age
    FROM
        users) AS users_temp

If someone can give me some hints about the best way to test the relative performance of these two methods empirically, that would be useful too

  • 1
    Just populate a table with 10K rows and time both methods. I think that would be as empirical as you can get. – Andriy M Sep 5 '16 at 11:47
  • Nested queries are slower than repeated function calls. – Rick James Sep 5 '16 at 17:44
1

You can do it with variables, like this:

SELECT
  user_id,
  year(@fecha:=from_days(datediff(now(), user_birthdate))) as years,
  month(@fecha) as months

But have caution with the execution order. If you assign the value in SELECT don't expect same value in WHERE part.

  • Bah, can't use variables in a view, which is a drag, but thanks anyway – cskilbeck Sep 5 '16 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.