0

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):

execution plan

Thanks in advanced!

1

Turn it into 4 UPDATES, one per status (EXPIRED, INCORRECT, ...). Run the updates in the correct order so that if two cases apply, you end up with the desired status.

Each UPDATE would match one of your cases. For example, for INCORRECT:

UPDATE tdetectable det
JOIN  tdetectable_equipment e  ON det.id = e.id_detectable 
JOIN  torganization org  ON det.id_organization = org.id
SET det.detectable_status = 'INCORRECT'
WHERE ( EXISTS(
        SELECT *
        FROM trevision_cycle
        WHERE id_detectable = e.id_detectable 
          AND status = 'NO_OK' 
          AND active <> 0
          AND id IS NOT NULL -- If `id` is the PK, this test unnecessary
      ) )
      AND det.active
      AND org.id_client = ?

Indexes:

torganization: INDEX(id_client, id)
det:  INDEX(id, active) -- unless you have PRIMARY KEY(id)
trevision_cycle: INDEX(id_detectable, status, id, active)

innodb_buffer_pool_size should be about 10G for that sized machine.

More specifics:

ALTER TABLE trevision_cycle ADD INDEX(id_detectable, status, id, active);

KEY and INDEX are synonyms. Most datatypes (INT, ENUM, etc, but not TEXT or BLOB) can be indexed. Since it has more than 1 column, this index is called "composite" or "compound". The order of columns in an index definition is important; I provided one useful order.

  • Thanks for your answer! For the indexes in the trevision_cycle table: The id is a primary index and the id_detectable is already a foreign key related to the tdetectable table. Should I also add a index of KEY type with the four columns? Also the status column is type ENUM, is it ok for the index or should I change it to something like CHAR? – Xtreme Biker Feb 16 '17 at 7:46
  • I answered you by adding to my Answer. – Rick James Feb 16 '17 at 16:27

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.