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 napisal(a):
> 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)

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 ¼g¹D¡G

> howa napisal(a):
> > 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)
>
> 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

okay, i know the reason now, thx anyway.

lain wrote:

> howa napisal(a):
>
>>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)
>
>
> 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
>

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 Austin ¼g¹D¡G

> lain wrote:
>
> > howa napisal(a):
> >
> >>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)
> >
> >
> > 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
> >
>
> 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
> )

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)?