Leyne, Sean wrote:

> Mitch,
>
>
>> After Anderson's post, I thought about replacing with a union. At
>>
> least
>
>> it could use the index I think would help. Perhaps a Stored procedure
>> might be even better.
>>
>
> My own review of you original post suggests:
>
> 1 - Do you have an index on Deal EditDate? (This could helps get rid of
> the NATURAL scan)
>
>

I do. It was this index that I wanted to use.

> 2 - You should create a DESCENDING compound index in Discussion,
> combining Deal_ID + ID). The optimizer should choose this index to
> resolve the MAX() criteria.
>
>

I thought of that. However, there should be few rows per "Deal", so,
not sure that would be a tremendous help, but is certainly worth a try.

> 3 - You should create compound indexes on Attachments and Discussions
> (combining DealID + CreateDate). The optimizer should choose these
> indexes over the current approach of trying to create a bit map by
> combining the separate indexes (FK_DISCUSSION_1/IDX_DISCUSSION_2 and
> FK_ATTACHMENT_2/IDX_ATTACHMENT_1).
>
>

another goo suggestion. Thanks, I'll try it.

> 4 - In addition to this, if you are running v2.0+, you might want to try
> using the DERIVED Table syntax for you query. The query would be:
>
>

Alas, I haven't made the switch yet.

> The Row Fetches are being executed within the engine.
>
>

then a moot point. Thanks. Very good points all.




[Non-text portions of this message have been removed]