• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Which Query is faster

 
Chiranjeevi Kanthraj
Ranch Hand
Posts: 290
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

or

[ July 23, 2008: Message edited by: Chiru Raj ]
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 290
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using MySql with Type as InnoDB
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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".
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic