At 01:06 PM 20/02/2004 +0000, you wrote:

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

You didn't supply a plan so we can't tell how the index was used. But the
query itself is *really horrible*.

Mainly you have mixed up SQL-89 and SQL-92 join syntaxes repeatedly, both
in the main query and in the subqueries. In fact, Firebird 1.5 will reject
such queries....

etc. etc.

Note dialect 3 won't accept a column name TYPE, tells me the db is dialect
1 - right?

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

WHERE
Customer_1.ID = 34056
AND UnifiedMes_1.TYPE = 37 /* BAD!! IN (37)) */
AND (
GalaxyObje_1.Created > :Param0
AND GalaxyObje_1.Created < :Param1)
/* should change date params to inclusive dates and use BETWEEN */
/* GalaxyObje_1.Created BETWEEN (:Param1 + 1) AND (:Param2 - 1) */
AND EXISTS (
SELECT Design_1.ID
FROM Design Design_1
JOIN DesignItem DesignItem_1
ON Design_1.ID = DesignItem_1.OwningDesign
JOIN SendableDesignItem SendableDe_1
ON SendableDe_1.ID = DesignItem_1.ID
JOIN UnifiedMessage UnifiedMes_2
ON SendableDe_1.ID = UnifiedMes_2.SendableDesignItem
WHERE UnifiedMes_2.ID = UnifiedMes_1.ID
AND (Design_1.Kind = 0)
)
)

Regarding the index on Created, it's hard to tell whether it would help but
keep it initially and see whether the second plan uses it (you will get
two plans, one for the main query and one for the subquery). After that,
disable the index and check whether it's faster without it.

I wouldn't hold my breath for a sub-second query with four tables in the
main query and four in a subquery, but you should be able to do a lot
better than 3 minutes!! :-)
/heLen