> What happens if you create the index on sCommunityID only? Does
> it still do the full table scan?
A bit difficult to drop the (sCommunityID, sTransactionID) index, as it's
the primary key.
> 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 ...
Hmmm. Something like this works:
SELECT sCommID, (SELECT sTransactionID
FROM TransactionList
WHERE sCommunityID = sCommID
ORDER BY sTransactionID Desc Limit 1) as MaxID
>From (SELECT 'a030600001bFi' As sCommID UNION ALL SELECT 'a030600004KDy'
As sCommID UNION ALL SELECT 'a030600008QhK' As sCommID)
The "explain" output shows that the Community table isn't being touched
now, and in fact it creates a temp table with the hard-coded values in it,
similar to "IN". So the final query may run faster (it's certainly very
fast with the data I have).
I might be working with a few thousand communities. Is there a limit to
the size of queries? In that case the "IN" clause would be better as it
uses less text.
Hugh
