How to speed up SQL query with multiple JOINs?

Question

The below SQL query took 8.0943 seconds to execute. Is there a better way to speed this up?

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

I have already indexed the following.

Table: people_attendance Columns: idno, date, time, employee, status, comment

Table: e_company_data Columns: idno, estatus

I might have done wrong on the indexes. Any help would be greatly appreciated. Thanks.

(From 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

Please provide `SHOW CREATE TABLE`. Offhand, I think that you need at least `INDEX(status, idno)`. — Rick James, May 06 '20 at 23:57
@RickJames You may check it here. https://pastebin.com/embed_js/UZunSJ4s Thanks. — xavier, May 07 '20 at 01:15

Gordon Linoff · Answer 1 · 2020-05-07T01:59:53.533

1

Possibly using window functions:

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;

This should benefit from indexes on people_attendance(idno, id desc) and e_company_data(status, idno).

EDIT:

For your version of the query:

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;

I would recommend indexes on e_company_data(status, idno) and people_attendance(idno, id).

edited May 07 '20 at 01:59

answered May 06 '20 at 20:44

Gordon Linoff

1,242,037
58
646
786

Thanks @Gordon but I got this error. "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY p.idno ORDER BY p.id DESC) as seqnum FROM people_attendance' at line 4." I'm using MySQL v5.7.29. This has something to do with the version? – xavier May 06 '20 at 21:09
1

@xavier MySql 5.7 might as well have been released in 2006, in terms of modern database features. You really want to get to an 8.0 release. – Joel Coehoorn May 06 '20 at 22:02
@JoelCoehoorn As much as I would like to but I currently do not have the power to do so, this is live on a shared hosting server but will try it locally. Thanks for your advice though. – xavier May 07 '20 at 01:17

Rick James · Accepted Answer · 2020-05-07T02:25:32.713

1

Give this a try:

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

The principle is to turn the correlated subquery into a derived table. Instead of 130 probes, it is one quick scan of a covering INDEX(idno, id) to get the 130 rows. After that, the rest is efficient JOINs.

Also, add INDEX(idno, status) (in either order) to e_company_data.

edited May 07 '20 at 02:25

answered May 07 '20 at 01:32

Rick James

135,179
13
127
222

Thanks. I got an error of nonaggregated column without GROUP BY. Is it recommended to turn off the warning message? I see some answers here: https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – xavier May 07 '20 at 02:16
@xavier - No. I goofed. See the `GROUP BY` that I added to the derived table. – Rick James May 07 '20 at 02:26
Just wow! Query took 0.0244 seconds. You're a legend. Thank you so much. – xavier May 07 '20 at 02:33

score 0 · Answer 3 · answered May 07 '20 at 02:15

0

In addition to Rick James answer, keep in mind that your query is slow on agregate function "SELECT MAX(id)". Think about to add field which on update would keep max(id).

answered May 07 '20 at 02:15

Gedza

1
2

How to speed up SQL query with multiple JOINs?

3 Answers3