[UPHPU] improving performance on an MySQL UNION

[Wade Preston Shearer]

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