I was going the recommend the same join constructs as Karol did. However, I am not sure why Karol suggested the construct of composite indicies in his reply as I thought you were going to create 2 additional indices, one for each of the fields in the multiple or clauses. As a result, this would not define a composite index unless there is something internally that Firebird would do that I am not aware of
In any case, if your query is currently working in terms of returning the correct data, the multiple and\or constructs are forcing the query to internally generate multiple if\select clauses, which will always add to the time for a query to complete. However, I found an article for designing indices when aggregate functions are used in a query. Though not related to the Firebird database engine, you may find it useful in terms of providing some insights to what you may elect to do to refine your query for faster speed... >>> See article at the link below https://stackoverflow.com/questions/50863962/speeding-up-queries-that-use-aggregate-functions-by-using-indexes https://stackoverflow.com/questions/50863962/speeding-up-queries-that-use-aggregate-functions-by-using-indexes <<< Though JOINS are historically rather inefficient ways to create queries (except for a LEFT INNER JOIN), in the case you provided, they may actually assist in speeding up the query as Karol suggested as a result of your multiple and\or clauses. Another point to consider is that I set up your query in a version 3.0.3 embedded database on my workstation along with the table definition you provided. I did this to see what the plan analyzer would provide. In this case it provided an interim SORT stage (as many such queries would), which will add significantly to your query when going through as many records as your table contains. However, the Firebird analyzer that my EMS Manager uses does not appear to be as detailed as that of other database engines I have used in the past. Steve Naidamast Sr. Software Engineer
