Hi Karl,
show us the plan as well as some information about index statistics.
That way it will be much easier for us to help you.

Set

--- 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