Announcement

Announcement Module
Collapse
No announcement yet.

Index question

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Index question

    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,
    Last edited by shade; 02-09-2014, 10:48 AM.

  • #2
    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?

    Comment


    • #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 |
      +----+-------------+-------+--------+---------------+--------------+---------+----------------+------+---------------------------------------+



      Comment


      • #4
        Hi,

        Glad to hear that your issue has been solved by removing "D.ID != 0" and using BETWEEN rather than < and >=

        Comment

        Working...
        X