I don't think the original query should take 1.5 minutes when your
maximum record count is 32k. You should try moving Customer
Customer_1 to your join clause. Some database engines will treat
this like a cross join, especially if you also have a join clause.
I have not tested this with Firebird. Anyway, assuming you have a
customerID column in your Unified_Message table you could try:

SELECT UnifiedMes_1.ID, UnifiedMes_1.TYPE
FROM UnifiedMessage UnifiedMes_1
JOIN Customer Customer_1
ON Customer_1.ID = UnifiedMes_1.CustomerID
JOIN Entity
Entity_1 ON (Entity_1.Customer = Customer_1.ID), GalaxyObject
GalaxyObje_1 ........ etc.


--- In firebird-support@yahoogroups.com, "karlzeift"
<kalle_privat@h...> wrote:

> Hi everyone!
>
> I'm using FB 1.0, and I've been satisfied for years with its
> performance and stability. I'm using it thorugh a framework, so I
> cant use triggers and sp:s.
>
> The problem I have now is that I'm running a pretty complex query
> several times, and I thought I should speed up the execution. All

of

> them takes about 1.5 mins (all are the same, just the parameters

are

> different). I have indexes on all id fields, but not on my

timestamp

> column (called Created in the query).
>
> To improve speed, I added a index on the column Created (type
> timestamp), and I was very surprised that it took much much longer
> time to execute the query, instead of 1.5 min, it took hours!!!!

And

> I dont have a lot of data, only 32K rows in the largest table!

Does

> any of you have an idea of how to speed this query up, since the
> index is slowing it down instead of speeding up???
>
> SELECT UnifiedMes_1.ID, UnifiedMes_1.TYPE
> FROM UnifiedMessage UnifiedMes_1, Customer Customer_1 JOIN
> Entity
> Entity_1 ON (Entity_1.Customer = Customer_1.ID), GalaxyObject
> GalaxyObje_1
> WHERE UnifiedMes_1.Entity = Entity_1.ID
> AND UnifiedMes_1.ID = GalaxyObje_1.ID
> AND (Customer_1.ID = 34056)
> AND (UnifiedMes_1.TYPE IN (37))
> AND (((GalaxyObje_1.Created > :Param0) and
> (GalaxyObje_1.Created <
> :Param1)) and EXISTS (SELECT Design_1.ID
> FROM Design Design_1, SendableDesignItem SendableDe_1 JOIN
> UnifiedMessage
> UnifiedMes_2 ON (SendableDe_1.ID =

UnifiedMes_2.SendableDesignItem)

> JOIN
> DesignItem DesignItem_1 ON (SendableDe_1.ID = DesignItem_1.ID)
> WHERE UnifiedMes_1.ID = UnifiedMes_2.ID
> AND Design_1.ID = DesignItem_1.OwningDesign
> AND (Design_1.Kind = 0)
> ))
>
> regards
>
> Karl