On 8/9/11 9:27 PM, Wade Preston Shearer wrote: > On 9 Aug 2011, at 21:16, thebigdog wrote: > >> On 8/9/11 7:31 PM, Wade Preston Shearer wrote: >>> It would be better if the database were architected slightly different, >>> but that's not an option at the present time. >>> >>> The following query is similar to one I posted several days ago. I am >>> wondering if anyone can offer any assistance on how to improve it's >>> performance. >>> >>> Here is the query: >>> >>> http://pastie.org/2347990 >>> >>> >>> Here is the EXPLAIN statement: >>> >>> http://pastie.org/2347981 >>> >>> >>> I think that I have indexes in all the right places, but I'm not as >>> experienced with UNIONS. Anything I can do to speed this up? It's taking >>> 5 seconds to run. >> >> You could try a couple things; however, unions just combine data. I would >> look at how to limit the results coming back on your sub queries and see if >> you could limit them by the id being used in your outer where clause: >> >> SELECT vote_id, c.char_name, c.char_id, vote_fb_id as fb_id FROM >> database.votes vote INNER JOIN database.charities c ON >> vote.vote_char_id=39 UNION ALL SELECT varc_id, c.char_name, c.char_id, >> varc_fb_id as fb_id FROM database.votes_archive varc INNER JOIN >> database.charities c ON varc.varc_char_id=39 >> >> >> Why can you do something like that? That might be a start. > > Sorry� the =39 shouldn't have been in there. It should return multiple rows. > I pasted the wrong query. I was testing with that. Here is the query: > > http://pastie.org/2348436
I would try and join the union tables (votes, votes_arc, charities) with the fb_users table and see how that works instead of combining them and then joining on it. thanks, -- thebigdog _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
