We are trying to change some data from our Mysql database (v5.7.17) from being evaluated on-the-fly to a field in the database. The concrete field is the detectable_status and it will be calculated each night for the equipment (which inherits from detectable) in order to evaluate it. That's the query updating the field for a concrete client in our application:
UPDATE tdetectable det JOIN tdetectable_equipment equipment ON det.id = equipment.id_detectable JOIN torganization org ON det.id_organization = org.id SET det.detectable_status = (CASE WHEN (equipment.setting_up_date IS NOT NULL AND equipment.expiration_periodicity_type IS NOT NULL AND fdate_periodicity(equipment.setting_up_date, equipment.expiration_periodicity_type, equipment.expiration_periodicity_value,1) < NOW()) = 1 THEN 'EXPIRED' WHEN (EXISTS( SELECT * FROM trevision_cycle incorrect_cycle WHERE equipment.id_detectable = incorrect_cycle.id_detectable AND incorrect_cycle.status = 'NO_OK' AND incorrect_cycle.active <> 0 AND incorrect_cycle.id IS NOT NULL)) THEN 'INCORRECT' WHEN (EXISTS( SELECT * FROM trevision_cycle need_revision_cycle WHERE equipment.id_detectable = need_revision_cycle.id_detectable AND need_revision_cycle.next_revision_date IS NOT NULL AND need_revision_cycle.next_revision_date < NOW() = 1 AND equipment.id_detectable = det.id AND need_revision_cycle.active <> 0)) THEN 'NEEDS_REVISION' WHEN (equipment.setting_up_date IS NULL AND (equipment.expiration_periodicity_type IS NOT NULL OR EXISTS( SELECT * FROM trevision_cycle last_revision_cycle WHERE last_revision_cycle.id_detectable = equipment.id_detectable AND last_revision_cycle.last_revision_date IS NULL AND last_revision_cycle.active <> 0))) THEN 'INDETERMINATE' ELSE 'CORRECT' END) WHERE det.active and org.id_client = ?
The status is set for every equipment in the client based in different factors (expiration dates, revision dates...). The problem is the query is quite slow. Here it is my benchmark:
Affected rows: 131, Duration: 0,375 sec. Affected rows: 4.208, Duration: 44,585 sec. Affected rows: 2.036, Duration: 13,712 sec. Affected rows: 13.288, Duration: 00:10:04
Also I want to clarify the content of the fdate_periodicity function, which takes a date and adds a periodicity to it (example: 2017-09-10 + 1 YEAR = 2018-09-10):
BEGIN IF add_to THEN RETURN CAST((CASE periodicity_type WHEN 'DAY' THEN (input_date + INTERVAL periodicity_value DAY) WHEN 'WEEK' THEN (input_date + INTERVAL periodicity_value WEEK) WHEN 'MONTH' THEN (input_date + INTERVAL periodicity_value MONTH) WHEN 'YEAR' THEN (input_date + INTERVAL periodicity_value YEAR) ELSE NULL END ) AS DATETIME ); ELSE RETURN CAST((CASE periodicity_type WHEN 'DAY' THEN (input_date - INTERVAL periodicity_value DAY) WHEN 'WEEK' THEN (input_date - INTERVAL periodicity_value WEEK) WHEN 'MONTH' THEN (input_date - INTERVAL periodicity_value MONTH) WHEN 'YEAR' THEN (input_date - INTERVAL periodicity_value YEAR) ELSE NULL END ) AS DATETIME ); end if; END
I understand MySql is doing quite a heavy work here, but are this execution times normal for a core-8 + 14Gb RAM machine? What could I do to improve it? Here it is the execution plan (explain):
Thanks in advanced!