--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

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

I've tried with only SQL-89 joins, but it makes no difference. I show
it below...

> Note dialect 3 won't accept a column name TYPE, tells me the db is

dialect

> 1 - right?

I'm a bit confused with the dialects. I use 3 in IBExpert, and I had
1 in TIBDatabase in Delphi. I changed the TIBDatabase to 3, but it
made no difference. The "type" column is not really called type, but
bold_type. I changed that manually before I posted.


I have now managed to get a plan for the initial query. Here it goes:

PLAN JOIN (UNIFIEDMES_2 INDEX (RDB$PRIMARY339),DESIGNITEM_1 INDEX
(RDB$PRIMARY326),SENDABLEDE_1 INDEX (RDB$PRIMARY372),DESIGN_1 INDEX
(RDB$PRIMARY325))

PLAN JOIN (CUSTOMER_1 INDEX (RDB$PRIMARY323),ENTITY_1 INDEX
(IX_ENTITY_CUSTOMER),UNIFIEDMES_1 INDEX
(IX_UNIFIEDMESSAGE_BOLD_TYPE,IX_UNIFIEDMESSAGE_ENTITY),GALAXYOBJE_1
INDEX (RDB$PRIMARY320))

I have removed the Created-index.

This is the sql-89 only version:

SELECT UnifiedMes_1.ID, UnifiedMes_1.TYPE
FROM Customer
Customer_1, Entity Entity_1, UnifiedMessage UnifiedMes_1,
GalaxyObject GalaxyObje_1
WHERE
Entity_1.Customer = Customer_1.ID
AND
UnifiedMes_1.Entity = Entity_1.ID
AND
UnifiedMes_1.ID = GalaxyObje_1.ID
AND
(Customer_1.ID = 37308)
AND
(UnifiedMes_1.TYPE IN (37))
AND
(((GalaxyObje_1.Created > '06/01/2003') and (GalaxyObje_1.Created
< '06/01/2004')) and EXISTS (SELECT Design_1.ID
FROM SendableDesignItem SendableDe_1, UnifiedMessage UnifiedMes_2,
Design Design_1, DesignItem DesignItem_1
WHERE UnifiedMes_1.ID = UnifiedMes_2.ID
AND SendableDe_1.ID = UnifiedMes_2.SendableDesignItem
AND SendableDe_1.ID = DesignItem_1.ID
AND Design_1.ID = DesignItem_1.OwningDesign
AND (Design_1.Kind = 0)
))

and its plan:

PLAN JOIN (UNIFIEDMES_2 INDEX (RDB$PRIMARY339),DESIGNITEM_1 INDEX
(RDB$PRIMARY326),DESIGN_1 INDEX (RDB$PRIMARY325),SENDABLEDE_1 INDEX
(RDB$PRIMARY372))

PLAN JOIN (CUSTOMER_1 INDEX (RDB$PRIMARY323),ENTITY_1 INDEX
(IX_ENTITY_CUSTOMER),UNIFIEDMES_1 INDEX
(IX_UNIFIEDMESSAGE_BOLD_TYPE,IX_UNIFIEDMESSAGE_ENTITY),GALAXYOBJE_1
INDEX (RDB$PRIMARY320))

Regars

Karl

> 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