Professional Web Applications Themes

MySQL group indexes - MySQL

I have a query like... SELECT ... ORDER BY A DESC, B DESC I have created both index(A), index(A,B) but mysql always explained that index(A) is used, although possible keys include (A,B) any reason to find out why? p.s.the numbers of rows is around 3M, (200MB in size)...

  1. #1

    Default MySQL group indexes

    I have a query like...

    SELECT ... ORDER BY A DESC, B DESC


    I have created both index(A), index(A,B)

    but mysql always explained that index(A) is used, although possible
    keys include (A,B)

    any reason to find out why?

    p.s.the numbers of rows is around 3M, (200MB in size)

    howa Guest

  2. #2

    Default Re: MySQL group indexes


    howa napisal(a): 

    this is because MySql uses only one (best in his opinion) index in
    GROUP BY - so if you want a double index on 2 columns (A, B) you must
    create it and group columns in order given in your index

    lain Guest

  3. #3

    Default Re: MySQL group indexes


    lain 寫道:
     
    >
    > this is because MySql uses only one (best in his opinion) index in
    > GROUP BY - so if you want a double index on 2 columns (A, B) you must
    > create it and group columns in order given in your index[/ref]

    okay, i know the reason now, thx anyway.

    howa Guest

  4. #4

    Default Re: MySQL group indexes

    lain wrote:
     
    >
    >
    > this is because MySql uses only one (best in his opinion) index in
    > GROUP BY - so if you want a double index on 2 columns (A, B) you must
    > create it and group columns in order given in your index
    >[/ref]

    If you have a where clause, the index is chosen by the columns in your "where"
    clause and not the group by, the group by is done in the sort phase after the
    data has been retrieved.

    --
    Michael Austin.
    Database Consultant
    )
    Michael Guest

  5. #5

    Default Re: MySQL group indexes


    Michael Austin 寫道:
     
    > >
    > >
    > > this is because MySql uses only one (best in his opinion) index in
    > > GROUP BY - so if you want a double index on 2 columns (A, B) you must
    > > create it and group columns in order given in your index
    > >[/ref]
    >
    > If you have a where clause, the index is chosen by the columns in your "where"
    > clause and not the group by, the group by is done in the sort phase afterthe
    > data has been retrieved.
    >
    > --
    > Michael Austin.
    > Database Consultant
    > )[/ref]

    yes, i know this now, thanks.

    in fact, are there any best practice to improve theese kind of query?
    since we are only selecting a small subset of the rows(i.e. paging)?

    howa Guest

Similar Threads

  1. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  2. Top words in Mysql Fulltext indexes
    By bzi999@gmail.com in forum MySQL
    Replies: 0
    Last Post: January 17th, 01:28 PM
  3. MYSQL query using GROUP BY and ORDER BY?
    By Bonge Boo! in forum PHP Development
    Replies: 3
    Last Post: August 15th, 06:12 PM
  4. mysql group??
    By Karzy in forum PHP Development
    Replies: 4
    Last Post: September 28th, 10:07 AM

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