Paul Lautman wrote:
> Michael Austin wrote:
>
>>Good Man wrote:
>>
>>
>>>onedbguru@yahoo.com wrote in news:1164392562.354785.112340
>>>@f16g2000cwb.googlegroups.com:
>>>
>>>
>>>
>>>>because you do not have an index on AdminID, SaveDate and SentDate.
>>>>
>>>>create index ue_idx on `UserEmails` (`AdminID`, `SaveDate`,
>>>>`SentDate`).....
>>>
>>>
>>>
>>>That doesn't help at all, and I can't see why it would considering
>>>that the table causing problems isn't UserEmails but ProjFiles....
>>>the ProjFiles table is the one being scanned.
>>>
>>>
>>
>>sorry, overlooked that in your description...
>>
>>What in your query makes some rows in ProjFiles more selectable?
>>Since you did not exclude or include any ProjFiles values in your
>>where clause, of course it needs to scan the whole table.
>
>
> I think you are mising his point Michael. He is doing a JOIN to that table.
> So the only records that need to be fetched from ProjFiles are those that
> have f.FileKey = ua.FileKey. So he is expecting the index to be used to
> locate the indovidual f.FileKey values, rather than having to scan the table
> for them.
>
>


Optimizers are a funny thing. They don't always do what we think they should do.
Maybe the cardinality of the selectivity is such that the optimizer determines
it can get the data faster by doing a FTS.

and doesn't he really want f.FileKey = ua.FileKey (as you stated), so, isn't
this backwards?

"LEFT JOIN ProjFiles f ON ua.FileKey=f.FileKey"

this, to me, says look at all records in f first, then match with ua. and since
there is no where-clause for f, it does a FTS. Order really does matter.

--
Michael Austin.
Database Consultant