Professional Web Applications Themes

Indexes And Sorting - MySQL

I'm running the following query on a table with an index on dateLast, and one on status: SELECT field FROM table WHERE (DATEDIFF(NOW(), dateLast) > 7) AND (status = 1) ORDER BY dateLast ASC LIMIT 10 This query takes about 80 seconds, because MySQL uses the index on 'status', which means it has to sort the rows using filesort. When I remove 'status' from the query: SELECT field FROM table WHERE (DATEDIFF(NOW(), dateLast) > 7) ORDER BY dateLast ASC LIMIT 10 The query doesn't even take a second, because MySQL can use the index on dateLast to both check the ...

  1. #1

    Default Indexes And Sorting

    I'm running the following query on a table with an index on dateLast,
    and one on status:

    SELECT field FROM table
    WHERE (DATEDIFF(NOW(), dateLast) > 7) AND (status = 1)
    ORDER BY dateLast ASC
    LIMIT 10

    This query takes about 80 seconds, because MySQL uses the index on
    'status', which means it has to sort the rows using filesort.

    When I remove 'status' from the query:

    SELECT field FROM table
    WHERE (DATEDIFF(NOW(), dateLast) > 7)
    ORDER BY dateLast ASC
    LIMIT 10

    The query doesn't even take a second, because MySQL can use the index
    on dateLast to both check the where condition and sort the rows.

    Any ideas? Is there a way to tell MySQL to order rows using a certain
    index?

    nk Guest

  2. #2

    Default Re: Indexes And Sorting

    On 10.05.2007 13:59, nk wrote: 

    Did you try a covering index on (dateLast, status) or (status, dateLast)?

    robert
    Robert Guest

  3. #3

    Default Re: Indexes And Sorting

    nk schrieb: 

    Yes, but that should be the last resort.
    It may be using the wrong index because its idea about the selectivity
    of each index is wrong; this can be rectified via YZE TABLE.

    Other than that, I'd use Robert Klemme's advice and use an index on
    (status, dateLast).

    You may also try computing the cut-off date first, then write something like
    SELECT field FROM table
    WHERE dateLast < [cutoff_date] AND (status = 1)
    ORDER BY dateLast ASC
    LIMIT 10

    I'm not sure that the optimizer knows that DATEDIFF is a monotonic
    function. If it doesn't, it cannot really use the dateLast field just
    because it is used in DATEDIFF(NOW(), dateLast) > 7.

    In general, non-monotonous expressions make using an index almost
    useless. Consider, for example,
    SELECT foo FROM t WHERE SIN(foo) BETWEEN 0.1 AND 0.2
    Using an index on foo is almost useless in this case. It's possible that
    mysql cannot tell the difference between SIN(_) and DATEDIFF(NOW(),_),
    and even adding another index might not change that.
    The cutoff_date trick will always work, of course :-)

    Regards,
    Jo
    Joachim Guest

  4. #4

    Default Re: Indexes And Sorting

    On Thu, 10 May 2007 14:06:13 +0200, Robert Klemme
    <com> wrote:
     

    From what i understood, (datelast,status) would be the best choice :
    - (datelast, status) lets you use the datelast index by itself,
    - (status,datelast) allows only using status and (status,datelast).

    So in that case where datelast seems to be important, better to have
    it first.
    subtenante Guest

  5. #5

    Default Re: Indexes And Sorting

    Using the (datelast, status) index would probably work, but
    unfortunately I also have an index on ('status','somethingelse') in
    the table, which I need for another query. Since this index's
    cardinality is lower than the one of (datelast, status), MySQL chooses
    the wrong index and I still have the same problem.

    Any idea how I can force MySQL to use a certain index?

    nk Guest

  6. #6

    Default Re: Indexes And Sorting

    On Tue, 05 Jun 2007 07:06:53 -0700, nk <com> wrote:
     

    SELECT blabla FROM table USE INDEX myindex WHERE ...
    or if it's really not willing to use it :
    SELECT blabla FROM table FORCE INDEX myindex WHERE ...
    subtenante Guest

Similar Threads

  1. indexes in cs
    By Albert_Constantineau@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 1
    Last Post: August 14th, 09:41 PM
  2. using indexes
    By tragik in forum Coldfusion Database Access
    Replies: 0
    Last Post: January 9th, 07:03 PM
  3. Indexes
    By tomL in forum Dreamweaver AppDev
    Replies: 2
    Last Post: March 14th, 04:26 PM
  4. Two indexes?
    By Andy_Fielding@adobeforums.com in forum Adobe Indesign Windows
    Replies: 1
    Last Post: August 14th, 02:55 AM
  5. sorting by multiple criterias (sub-sorting)
    By Tom in forum PERL Miscellaneous
    Replies: 3
    Last Post: October 11th, 05:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139