Re: Optimizing query

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.

You're not necessarily getting the latest entry, just the one with the
highest "id". Sequences guarantee uniqueness but if you have concurrent
inserts not necessarily ordering.

> The table contains around 1 million records and the query takes around 2
> seconds.

Well, you don't say how many different values for "node" there are, nor
how many rows you would expect where node='10'.

> I have tried to make an index on node and also on both id & node, but is
> doesn't lower the query time.

Difficult to say what's happening since you don't supply any EXPLAIN
ANALYSE output.

However, if you have an index on (node,id) you might want to try:
SELECT ... ORDER BY node DESC, id DESC LIMIT 1;
That way the "ORDER BY" part clearly tells the planner that a
reverse-order on your index will be useful.

--
Richard Huxton
Archonet Ltd