La requête SQL ci-dessous a pris 8,0943 secondes pour s'exécuter. Y a-t-il une meilleure façon d'accélérer cela?

SELECT 
       e.idno, e.estatus,
       p.idno, p.id, p.time, p.date, p.employee, p.status, p.comment
       FROM e_company_data e
       INNER JOIN people_attendance p ON p.idno = e.idno
       WHERE p.id = (SELECT MAX(id) FROM people_attendance p1
                            WHERE p1.idno = p.idno)
       AND e.estatus = 1 ORDER BY e.idno

J'ai déjà répertorié ce qui suit.

Table: people_attendance Colonnes: idno, date, heure, employé, statut, commentaire

Table: Colonnes e_company_data: idno, statut

J'ai peut-être mal fait sur les index. Toute aide serait grandement appréciée. Merci.

(De pastebin)

CREATE TABLE `people_attendance` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `reference` int(11) DEFAULT NULL,
 `idno` varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `date` date DEFAULT NULL,
 `employee` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `status` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `time` time DEFAULT NULL,
 `comment` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `reason` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
 `counter` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idxidno` (`idno`),
 KEY `idxattendance` (`employee`,`status`,`date`,`time`,`comment`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=12888 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `e_company_data` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `reference` int(11) NOT NULL,
 `company` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `department` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'0\',
 `jobposition` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `companyemail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `idno` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `pin` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `startdate` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `dateregularized` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `reason` varchar(455) COLLATE utf8mb4_unicode_ci DEFAULT \'\',
 `leaveprivilege` int(11) DEFAULT NULL,
 `estatus` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idxcompdata` (`idno`,`department`,`estatus`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=130 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
0
xavier 6 mai 2020 à 23:32

3 réponses

Meilleure réponse

Essayez ceci:

SELECT  e.idno, e.estatus, p.idno, p.id, p.time, p.date, p.employee,
        p.status, p.comment
    FROM  ( SELECT idno, MAX(id) AS last_id
                 FROM people_attendance
                 GROUP BY idno ) AS x
    JOIN  e_company_data e  USING(idno)
    JOIN  people_attendance p  ON p.id = x.last_id
    WHERE  e.estatus = 1
    ORDER BY  e.idno

Le principe est de transformer la sous-requête corrélée en une table dérivée. Au lieu de 130 sondes, il s'agit d'une analyse rapide d'une couverture INDEX(idno, id) pour obtenir les 130 lignes. Après cela, le reste est des JOINs efficaces.

Ajoutez également INDEX(idno, status) (dans l'un ou l'autre ordre) à e_company_data.

1
Rick James 7 mai 2020 à 02:25

Utilisation possible des fonctions de fenêtre:

SELECT e.idno, e.estatus,
       p.idno, p.id, p.time, p.date, p.employee, p.status, p.comment
FROM e_company_data e JOIN
     (SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.idno ORDER BY p.id DESC) as seqnum
      FROM people_attendance p
     ) p
     ON p.idno = e.idno AND seqnum = 1
WHERE e.estatus = 1
ORDER BY e.idno;

Cela devrait bénéficier des index sur people_attendance(idno, id desc) et e_company_data(status, idno).

ÉDITER:

Pour votre version de la requête:

SELECT e.idno, e.estatus,
       p.idno, p.id, p.time, p.date, p.employee, p.status, p.comment
FROM e_company_data e JOIN
     people_attendance p
     ON p.idno = e.idno
WHERE p.id = (SELECT MAX(p2.id)
              FROM people_attendance p2
              WHERE p2.idno = p.idno
             ) AND
      e.estatus = 1
ORDER BY e.idno;

Je recommanderais des index sur e_company_data(status, idno) et people_attendance(idno, id).

1
Gordon Linoff 7 mai 2020 à 01:59

En plus de la réponse de Rick James, gardez à l'esprit que votre requête est lente sur la fonction agrégée "SELECT MAX (id)". Pensez à ajouter un champ qui, lors de la mise à jour, conserverait max (id).

0
Gedza 7 mai 2020 à 02:15