Order of Where Clauses

foosh101 · Post by **foosh101** » Fri Mar 14, 2003 10:10 am

I have several where clauses in my select statements in my vortex scripts. From what I have read, vortex executes the where clauses from left to right. I am trying to optimize the performance of my queries, and based on what I understand about the way texis executes queries, it would make sense that the proper ordering of the where clauses could increase performance. For example, maybe doing a search on an integer column first, would allow texis to resolve the query faster and reduce the set of possible results for the next, maybe slower, where clause. Or maybe a general rule might be to always put the where clause that will reduce the result set the most first. I guess I am just looking to see if you have any rules/suggestions/guideline on ordering the where clauses in my SQL statements. Assume all fields are indexed. -Thanks.

Post by **John** » Fri Mar 14, 2003 10:59 am

You are correct, the ordering of the clauses does make a difference. The basic rule is to put the most restrictive clauses first, although in general a LIKE clause should go first, especially if there is a compound metamorph index, in which case it can resolve multiple clauses at the same time. Some more discussion is at: http://www.thunderstone.com/texis/site/ ... auses.html

foosh101 · Post by **foosh101** » Fri Mar 14, 2003 11:13 am

oops, sorry about the double post. That Tutorial is awesome. This is the first time I have seen it. How do you normally get to that from the the main site. I have never seen a link to it. There is some really valuable information in there. I wish I had of known about it earlier.

foosh101 · Post by **foosh101** » Fri Mar 14, 2003 11:20 am

are there any other resources that I should know about that might be of help. Anything at all, as I enjoy all the information I can get.

Post by **John** » Fri Mar 14, 2003 11:53 am

If you follow the Tech Support link then it is at the top of the page. The other place to look for resources is the demos page, which has the source code to the demos available to look at.