Which Query is faster

[Chiranjeevi Kanthraj]

   Select * from table order by primaryColomn desc limit 1 

or

   selct * from table group by  primaryColomn haveing =(select max(primaryColomn ) from table)

[ July 23, 2008: Message edited by: Chiru Raj ]

[Ulf Dittmer]

That's impossible to say in general because it depends on which DB is used, which indices are in place etc. What did your tests indicate?

[Chiranjeevi Kanthraj]

I am using MySql with Type as InnoDB

[Paul Sturrock]

Again, this is still impossible to answer. Query performance is largely driven by: the data you have in your database, the indices you have defined, the state of those indices etc.

I could make a wild guess that the second query may be slower, since this uses a subquery. But the only real way to know is to test it. You should do that.

[Jeanne Boyarsky]

My gut says the opposite - I would think the second is faster if you have an index on primaryColumn. The execution plans would be different for both scenarios.

First:
1) Retrieve all rows from disk (sorting by primaryColumn if no index is returned)
2) Return the first one across the network
3) Hope the db is smart enough to retrieve all the rows before discarding them (many are not)

Second:
1) Go directly to the max row via a sorted index
2) Retrieve the one row from disk
3) Return the one row across the network

As you can see, it is possible to speculate that either is faster. Paul and I bother have different gut instincts. All that matters is what is faster for you of course. Which means you'll have to try it.

[Scott Selikoff]

I'm going to go back and sec and say you really need to run tests on real data (not test data). Query optimizes are magically creatures that may or may not use indexes properly. In other words, while the proper indexes should guarantee a near-optimal query path, there's no guarantee a particular DBMS system will find the path; since most query optimizes do not have the time to search all paths.

The only piece of advice I can give you is to avoid nested queries. In general, query optimizes make better use of search trees with joins rather than nested queries, although in the case of max/min, I'm not sure you can avoid the subquery other than sorting your results.

Also, some systems support a third query "SELECT TOP 1 * FROM table order by primaryColumn desc".