0

I have two SQL queries that are structurally the same, but have a significant difference in execution time. The first query takes around 500ms to execute, while the second one takes only 50ms. Both queries aim to summarize some prices and group the results by month. The main difference lies in how they join the caregroup table.

The first query uses a function getgroupatgivendate() in an INNER JOIN, which is causing the delay (500ms):

INNER JOIN caregroup c2_ ON (getgroupatgivendate (DATE_FORMAT(b1_.billing_period, '%Y-%m-%d'), c3_.id) = c2_.id)

The second query replaces this function with a subquery in the JOIN condition and executes much faster (50ms):

INNER JOIN caregroup c2_ ON (SELECT group_id FROM children_group WHERE child_id = c3_.id AND DATE_FORMAT(b1_.billing_period, '%Y-%m-%d')
    BETWEEN IFNULL(validfrom, '1900-01-01') AND IFNULL(validuntil, '2099-12-31')
    ORDER BY validfrom DESC LIMIT 1) = c2_.id

Is there anything I can do that a join over a function has the same performance?

Improve this question

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.