Re: Optimizing query

On Mon, 15 Aug 2005, Poul Møller Hansen wrote:

> I have a problem creating a usable index for the following simple query:
> SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
>
> id is a serial, so the query is to find the latest entry to a given node
> and id is the primary key.
> The table contains around 1 million records and the query takes around 2
> seconds.
>
> I have tried to make an index on node and also on both id & node, but is
> doesn't lower the
> query time.

Try to make an index on (node,id) and write the query as:

SELECT * FROM my.table WHERE node = '10' ORDER BY node desc, id desc LIMIT 1;

Then i'm pretty sure it will use that index.

--
/Dennis Björklund