>Hi! Probably these questions were asked before, but I can't find nothing on the
>web (perhaps I'm not seeking properly)
>
>1) Having some clauses concatenated with "and" in a where statement, does it
>matter the order of the clauses? I mean: I always tend to put in the first order
>the clauses that have an index (ie: PK) and then the clauses that don't have
>index. Is that OK? or Firebird just "know" what sentence is better to evaluate
>first?

I never care about the order of clauses that are ANDed. One slightly similar thing that I do care about, is to put [INNER] JOINs above [LEFT | RIGHT][OUTER] JOINs since I've noticed (at least in Fb 1.5, I rarely use 2.0, 2.1 or 2.5) that the optimizer then more freely can choose the order of the inner joined tables in the plan.

>2) Does the "or" operator decrease the performance? I have some where like this:
>
>where (ID = :ID) or (:ID = -1)
>
>So I pass -1 when I want all the records. Is that OK? Or is better to
>reconstruct the select in the client, depending what I want '

I generally consider this a lousy construct that prevents the use of any index on the ID field. Why? Well, the use of indexes is determined at prepare time and that is before the parameter value is known. Using the index would slow things down if the value was -1, otherwise things would normally (depending on other parts of the select) be quicker when using the index. Hence, I normally use this kind of construct only for smallish lookup tables (or rather, I tend to write

where ID = coalesce(:ID, ID)

but that's basically the same thing only using <NULL> rather than -1). For larger tables, I'd rather create the WHERE clause dynamically (or have IF .. ELSE.. if in trigger or stored procedure), so that the index would be used when :ID was supplied and not used otherwise.

HTH,
Set