I was looking for a way to greatly optimize a query linking two types of documents : persons and time sheets. Work site managers input time sheets for their employees on work sites, then, a table is built for wage managers using these time sheets.
Our client wants to add a warning to the table when there haven’t been any timesheets in the 90 days before the displayed month. To do so, I directly used the persons’ id and an EXISTS correlated subquery because I wanted to avoid GROUP BY or JOINs but I may not have understood correctly how to use
Below I wrote the query I was testing but
USE KEYS references the person’s document instead of allowing me to test if they have at least one time sheet during the given period.
SELECT p AS `id_personne`, EXISTS( SELECT t.* FROM `bucket` t USE KEYS p WHERE t.`type` = 'pointage' AND t.`id_personne` = p AND t.`id_chantier` = 'work site id' AND t.`date_imputation` BETWEEN 1625781600000 AND 1633557599999 AND t.`est_valide` IN [1, 2] ) AS `pointage` FROM [ 'person id 1', 'person id 2', 'person id 3' ] AS p
Did I misunderstand how to use
USE KEYS? Is what I want to do even possible? I even would like to do this query with a different list of work sites per person (person 1 on work site 1 and 2, person 2 on work site 2 and 3, etc) but I wanted to try simpler first.
Thanks and have a good day!