[Solved] MySQL - Write an UPDATE Query based on SELECT Query on multiple tables

EverSQL Database Performance Knowledge Base

MySQL - Write an UPDATE Query based on SELECT Query on multiple tables

Database type:

I am far from being strong in MySQL or database, so I am using a tool called FlySpeed SQL Query. This tool helps me graphically created MySQL queries. Here is the query I got created with this tool and a lot of reading on the internet.

Select
  Employee.Firstname As Prénom,
  Employee.Name As NOM,
  TimeSheet.Filled As Validé,
  TimeSheet.Closed As Clôturé,
  Sum(Imputation.Hours) As `Somme des heures`,
  TimeSheet.Month + 1 As Mois,
  TimeSheet.Year As Année
From
  Employee Inner Join
  TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
  Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
  Project On Imputation.Project_Id = Project.Id
Where
  TimeSheet.Filled = '1' And
  (TimeSheet.Closed = '0' Or
    TimeSheet.Closed Is Null) And
  Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01') And
  Project.Id != '1'
Group By
  Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
  Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
Order By
  Année,
  Mois,
  NOM

This query returns me exactly the desired result. Keeping the same conditions as the MySQL query above, I want to update the Closed field to "1". I would like to make some things that taste there:

-- UPDATE Query
--
UPDATE TimeSheet
SET Closed = '1'
--
-- UPDATE Query
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
Project On Imputation.Project_Id = Project.Id
--
-- With those conditions
--
Where
TimeSheet.Filled = '1' And
(TimeSheet.Closed = '0' Or
    TimeSheet.Closed Is Null) And
-- Calculating a time range
Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01') 
And
Project.Id != '1'
Group By
Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
-- Calculation : >= 5 times the number of days in the period
Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
---
-- With those conditions

So I need help to turn my SELECT query to an UPDATE query. Feel free to ask me for more information.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Use Numeric Column Types For Numeric Values (query line: 21): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  3. Use Numeric Column Types For Numeric Values (query line: 23): Referencing a numeric value (e.g. 0) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  4. Use Numeric Column Types For Numeric Values (query line: 28): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
ALTER TABLE `Employee` ADD INDEX `employee_idx_id` (`Id`);
ALTER TABLE `Imputation` ADD INDEX `imputation_idx_timesheet_id_day` (`TimeSheet_Id`,`Day`);
ALTER TABLE `Project` ADD INDEX `project_idx_id` (`Id`);
ALTER TABLE `TimeSheet` ADD INDEX `timesheet_idx_filled_closed` (`Filled`,`Closed`);
The optimized query:
SELECT
        Employee.Firstname AS Prénom,
        Employee.Name AS NOM,
        TimeSheet.Filled AS Validé,
        TimeSheet.Closed AS Clôturé,
        Sum(Imputation.Hours) AS `Somme des heures`,
        TimeSheet.Month + 1 AS Mois,
        TimeSheet.Year AS Année 
    FROM
        Employee 
    INNER JOIN
        TimeSheet 
            ON TimeSheet.Employee_Id = Employee.Id 
    INNER JOIN
        Imputation 
            ON Imputation.TimeSheet_Id = TimeSheet.Id 
    INNER JOIN
        Project 
            ON Imputation.Project_Id = Project.Id 
    WHERE
        TimeSheet.Filled = '1' 
        AND (
            TimeSheet.Closed = '0' 
            OR TimeSheet.Closed IS NULL
        ) 
        AND Imputation.Day <= Last_Day(Current_Date - INTERVAL 1 Month) 
        AND Imputation.Day >= Date_Format(Current_Date - INTERVAL 1 Month, '%Y-%m-01') 
        AND Project.Id != '1' 
    GROUP BY
        Employee.Name,
        TimeSheet.Month + 1,
        TimeSheet.Year 
    HAVING
        Sum(Imputation.Hours) >= 5 * (
            (
                DateDiff(Last_Day(Current_Date - INTERVAL 1 Month), Date_Format(Current_Date - INTERVAL 1 Month, '%Y-%m-01')) + 1
            )
        ) 
    ORDER BY
        Année,
        Mois,
        NOM

Related Articles



* original question posted on StackOverflow here.