Re: Slooooow query question

> This isn't realy a *slow* question, but a question about slow queries. > :) > Anyway, I'm trying to do a query such as the following: > > SELECT x FROM y ORDER BY x> > In this example, 'x' is a "primary key" with a unique index. As you would > expect, this returns rows immediately. However, when I make the following > change, there is a long pause as Informix examines the entire table: > > SELECT DISTINCT x FROM y ORDER BY x DISTINCT is evaluated by copying all the relevant tuples to a temporary table and then grouping them. The ORDER BY is evaluated last; the engine has to sort the rows (writing them to $DBTEMP first), because the temp table hasn't got an index on it. > I've tried this query under Informix OnLine 5.0 and 6.0, with the same > results. I don't know of a version that has implemented any short cuts. > Is there any way to speed up a DISTINCT query? Am I just out of luck? No, and yes respectively. Unless there's some kind of shortcut in the latest version(s). You'd expect it to realise it was looking at a unique index wouldn't you. I suggest you rip out the DISTINCT and add a GROUP BY x. That way the grouping will be evaluated through the index as well. DISTINCT is nearly always more expensive than people imagine. Sloppy use of DISTINCT is one of the commonest causes of poor application performance. If you want to see a real howler try creating a view as SELECT DISTINCT, then query through it on a primary key. akent@cix.compulink.co.uk (Andy Kent) ------------------------------------------------ Freelance Informix Database Specialist, Redland, Bristol, England