Hi Russell
On Tue, 8 May 2012, Arjen Lentz <arjen@lentz.com.au> wrote:
There's a join in there already, as indicated by the comma in the table list. A comma does an inner join, and the join condition then needs to be specified in the WHERE clause. It's a common/old way of phrasing joins, and I tend to advise developers to use explicit JOINs (SELECT ... a JOIN b ON (condition) ...) as they're intrinsically clearer and prevent mishaps. Functionally they're the same.
Thanks for the suggestion, I tried a join and it didn't do much good. I've attached the result of the explain commands. A default join sorts through 18M rows. A right join is better and only sorts through 1M rows but gets the data four times (that account has four aliases) which adds to processing for the program that receives it.
I think that perhaps I should coalesce the results from the aliases table before checking against lists, but I can't figure out how to do that.
Any further suggestions?
The EXPLAINs indicate the 'index" access type which is an index scan. Not what you want. But it's a consequence of doing what you do, which is ignoring the advice that you were given: use a UNION to do this construct. Regards, Arjen. -- Exec.Director @ Open Query (http://openquery.com) MySQL services Sane business strategy explorations at http://Upstarta.biz Personal blog at http://lentz.com.au/blog/