Not the answer you need?
Register and ask your own question!

Index question

shadeshade EntrantCurrent User Role Novice
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,

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Generally, you can use FORCE INDEX (http://dev.mysql.com/doc/refman/5.5/en/index-hints.html) 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?
  • shadeshade Entrant Current User Role Novice
    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 |
    +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +
  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Glad to hear that your issue has been solved by removing "D.ID != 0" and using BETWEEN rather than < and >= :)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.