Index question

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I’m trying to make the right index, to speed up the following query:

SELECT
FROM_DAYS(TO_DAYS(D.timein)) as date,
TO_DAYS(D.timein) as day, DATE_FORMAT(D.timein, ‘%H:%i’) AS timein,
DATE_FORMAT(D.timeout, ‘%H:%i’) AS timeout,
D.locID AS location,
(UNIX_TIMESTAMP(timeout)-UNIX_TIMESTAMP(timein))/60 as timemins,
D.adjustment,
D.note1,
D.note2,
F.ID AS fid,
F.description AS fdesc,
F.factor AS ffactor,
F.hours AS fhours, ((TO_DAYS(D.timein) % 7) >= 2) AS weekday,
(TO_DAYS(D.timein) % 7) AS dayno
FROM tid D
LEFT JOIN tidtypes F ON F.ID = D.tidID
WHERE
D.userID = 2
AND D.timein < DATE_ADD(‘2014-02-09’, INTERVAL 1 DAY)
AND D.timein >= ‘2014-02-02’
AND D.ID != 0
ORDER BY date, F.ID DESC, timein

I’m using Percona Server 5.6, and have made a index with the fields that are used to limit the query with in the order they are accessed:

Table TID:

Index: tidid, userid, timeid, id:

But the server does not use the index, the result of explain is:

±—±------------±------±-------±--------------±--------±--------±---------------±-----±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-------±--------------±--------±--------±---------------±-----±----------------------------+
| 1 | SIMPLE | D | range | PRIMARY | PRIMARY | 3 | NULL | 2935 | Using where; Using filesort |
| 1 | SIMPLE | F | eq_ref | PRIMARY | PRIMARY | 1 | phptid.D.tidID | 1 | NULL |
±—±------------±------±-------±--------------±--------±--------±---------------±-----±----------------------------+

Can someone give me pointers to what the correct indexes for the above SQL would be, and why.

Thanks in advance for your input :slight_smile:

Regards,

2

Hi,

Generally, you can use FORCE INDEX ([url]http://dev.mysql.com/doc/refman/5.5/en/index-hints.html[/url]) if mysql optimizer doesn’t use proper index. But in your case, it seems, its not using because of " D.ID != 0" . Can you try to add columns one by one in composite index and check if its using index? like (tidid, userid) then (tidid, userid, timeid) ?
Is it possible for you to use between rather then < and >= with timeid?

3

The D.ID != 0 seems unneeded, weird thing when removed it doubles the number of row the query examines…

But now I got it to choose a index, I changed the query to:

SELECT
FROM_DAYS(TO_DAYS(D.timein)) as date,
TO_DAYS(D.timein) as day, DATE_FORMAT(D.timein, ‘%H:%i’) AS timein,
DATE_FORMAT(D.timeout, ‘%H:%i’) AS timeout, D.locID AS location,
(UNIX_TIMESTAMP(timeout)-UNIX_TIMESTAMP(timein))/60 as timemins,
D.adjustment, D.note1, D.note2, F.ID AS fid, F.description AS fdesc,
F.factor AS ffactor, F.hours AS fhours, ((TO_DAYS(D.timein) % 7) >= 2) AS weekday,
(TO_DAYS(D.timein) % 7) AS dayno
FROM tid D
LEFT JOIN tidtypes F ON F.ID = D.tidID
WHERE
D.userID = 2
AND D.timein BETWEEN ‘2014-02-02’ AND ‘2014-02-10’
ORDER BY
date, timein

Removed the D.ID != 0 and an also unneeded F.ID DESC in ORDER BY, and changed to the BETWEEN method.

Made a report_index on (userID, timein) in the order used in the where, and now the explain looks alot better:

±—±------------±------±-------±--------------±-------------±--------±---------------±-----±--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-------±--------------±-------------±--------±---------------±-----±--------------------------------------+
| 1 | SIMPLE | D | range | report_index | report_index | 7 | NULL | 11 | Using index condition; Using filesort |
| 1 | SIMPLE | F | eq_ref | PRIMARY | PRIMARY | 1 | phptid.D.tidID | 1 | NULL |
±—±------------±------±-------±--------------±-------------±--------±---------------±-----±--------------------------------------+

4

Hi,

Glad to hear that your issue has been solved by removing “[COLOR=#252C2F]D.ID != 0” and using BETWEEN rather than < and >= :slight_smile: