Query optimization suggestions

Hi all,

I have a table like that:


articles {

cat INT(10) UNSIGNED
id INT(10) UNSIGNED
aorder INT(10) UNSIGNED
lastupdated INT(10) UNSIGNED
}


I want to query something like:

SELECT * FROM articles
WHERE cat = 9 AND aorder >= 0
ORDER BY aorder DESC, lastupdated DESC
LIMIT 13095, 10

the query is quite slow when number of records in the articles say,
more than 1M row

currently, index = (cat, aorder, lastupdated)

any suggestion to improve or re-design the table?

thanks.

howa wrote:
> Hi all,
>
> I have a table like that:
>
>
> articles {
>
> cat INT(10) UNSIGNED
> id INT(10) UNSIGNED
> aorder INT(10) UNSIGNED
> lastupdated INT(10) UNSIGNED
> }
>
>
> I want to query something like:
>
> SELECT * FROM articles
> WHERE cat = 9 AND aorder >= 0
> ORDER BY aorder DESC, lastupdated DESC
> LIMIT 13095, 10
>
> the query is quite slow when number of records in the articles say,
> more than 1M row
>
> currently, index = (cat, aorder, lastupdated)
>
> any suggestion to improve or re-design the table?
>
> thanks.
Does an explain say that the index is being used for the sort?

If not try ORDER BY cat DESC, aorder DESC, lastupdated DESC; in order
(no pun intended) to help the optimiser to realise what it should be
doing.

Captain Paralytic ¼g¹D¡G

> howa wrote:
> > Hi all,
> >
> > I have a table like that:
> >
> >
> > articles {
> >
> > cat INT(10) UNSIGNED
> > id INT(10) UNSIGNED
> > aorder INT(10) UNSIGNED
> > lastupdated INT(10) UNSIGNED
> > }
> >
> >
> > I want to query something like:
> >
> > SELECT * FROM articles
> > WHERE cat = 9 AND aorder >= 0
> > ORDER BY aorder DESC, lastupdated DESC
> > LIMIT 13095, 10
> >
> > the query is quite slow when number of records in the articles say,
> > more than 1M row
> >
> > currently, index = (cat, aorder, lastupdated)
> >
> > any suggestion to improve or re-design the table?
> >
> > thanks.
> Does an explain say that the index is being used for the sort?
>
> If not try ORDER BY cat DESC, aorder DESC, lastupdated DESC; in order
> (no pun intended) to help the optimiser to realise what it should be
> doing.

the optimizer said the index - (cat, aorder, lastupdated) is being
used, rows involved is around 60K (out of 2M rows) - becasue actually
the number of rows satisifed the conidition is really 60K...

but i only want to take 15 rows out of them (due to paging)

are there anyway to rewrite the query can improve the result?