What happens if you create the index on sCommunityID only? Does
it still do the full table scan?
Also, don't overlook using UNION or UNION ALL, ugly as they
can be. Maybe something like this could be used to avoid creating
a very small temporary table:
SELECT ... FROM (SELECT ... UNION SELECT ... UNION SELECT ...), TransactionList
WHERE ...
--- Hugh Gibson <[EMAIL PROTECTED]> wrote:
> I have this query:
>
> SELECT sCommunityID, max(sTransactionID)
> FROM TransactionList
> WHERE sCommunityID in ('a030600001bFi','a030600004KDy', 'a030600008QhK')
> GROUP BY sCommunityID
>
> There is an index on (sCommunityID, sTransactionID)
>
> This forces a table scan (perhaps improved in 3.2.6).
>
> I can use a LIMIT 1 clause like this, but only with one community ID:
>
> SELECT sCommunityID, sTransactionID
> FROM TransactionList
> WHERE sCommunityID = 'a030600001bFi'
> ORDER BY sTransactionID Desc LIMIT 1
>
> Is there a way of getting the latter to work with multiple sCommunityIDs?
>
> Hugh
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
