# Index question
**URL:** https://forums.percona.com/t/index-question/3255
**Category:** Other MySQL® Questions
**Created:** [February 6, 2014, 11:04am UTC](https://forums.percona.com/t/index-question/3255 "2014-02-06T11:04:37Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@shade](https://forums.percona.com/u/shade)
#### Post date: [February 6, 2014, 11:04am UTC](https://forums.percona.com/t/index-question/3255/1 "2014-02-06T11:04:37Z")
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 🙂
Regards,
---
### Author:  [@niljoshi](https://forums.percona.com/u/niljoshi)
#### Post date: [February 12, 2014, 5:01am UTC](https://forums.percona.com/t/index-question/3255/2 "2014-02-12T05:01:26Z")
Hi,
Generally, you can use FORCE INDEX ([url][http://dev.mysql.com/doc/refman/5.5/en/index-hints.html[/url]](http://dev.mysql.com/doc/refman/5.5/en/index-hints.html%5B/url%5D)) 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?
---
### Author:  [@shade](https://forums.percona.com/u/shade)
#### Post date: [February 18, 2014, 2:22pm UTC](https://forums.percona.com/t/index-question/3255/3 "2014-02-18T14:22:36Z")
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 |
±—±------------±------±-------±--------------±-------------±--------±---------------±-----±--------------------------------------+
---
### Author:  [@niljoshi](https://forums.percona.com/u/niljoshi)
#### Post date: [February 24, 2014, 1:38am UTC](https://forums.percona.com/t/index-question/3255/4 "2014-02-24T01:38:43Z")
Hi,
Glad to hear that your issue has been solved by removing “[COLOR=#252C2F]D.ID != 0” and using BETWEEN rather than \< and \>= 🙂