It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Which Query is faster Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Which Query is faster" Watch "Which Query is faster" New topic

Which Query is faster

Chiranjeevi Kanthraj
Ranch Hand

Joined: Feb 18, 2008
Posts: 290


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

Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
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
Ranch Hand

Joined: Feb 18, 2008
Posts: 290

I am using MySql with Type as InnoDB
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

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.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33124

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.

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)

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

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".

[OCA 8 Book] [Blog]
I agree. Here's the link:
subject: Which Query is faster
It's not a secret anymore!