On Wed, Jul 20, 2011 at 9:59 AM, Wade Preston Shearer <[email protected]> wrote: > Is there a better way to write the following query? > > SELECT COUNT(*) as votes, combined.char_name > FROM ( > SELECT vote_id, c.char_name > FROM database.votes vote > INNER JOIN database.charities c > ON vote.vote_char_id=c.char_id > WHERE vote.vote_voter_id=' . intval($_POST['voter_id']) . ' > > UNION ALL > > SELECT varc_id, c.char_name > FROM database.votes_archive varc > INNER JOIN database.charities c > ON varc.varc_char_id=c.char_id > WHERE varc.varc_voter_id=' . intval($_POST['voter_id']) . ' > ) combined > GROUP BY combined.char_name > > > There are three tables: charities, today's votes, and a vote archive. The > votes and votes_archive tables have the same columns (with names). The > results I am looking for is a list of all the charities the individual has > voted for with a count of how many times he has voted for each. So, if he has > voted for the Boys and Girls Club twice, the MS Support Foundation seven > times, and the BSA once, it'd return: > > 2, Boys and Girls Club > 7, MS Support Foundation > 1, BSA > > > The query above works and returns that information. Is that the best way to > do that though?
I think this would pull the list for every voter: (I tried it at small scale, but haven't tested it well) select count(*) as votes, c.char_name, v.vote_voter_id from charities c join (select * from votes union all select * from votes_archive) v on c.char_id=v.vote_char_id group by c.char_id, v.vote_voter_id order by v.vote_voter_id, c.char_id; You could add "where v.vote_voter_id=NNNN" in the outer query, or add "where vote_voter_id=NNNN" and "where varc_voter_id=NNNN" on the queries in the subquery union to restrict it to just pull one. Performance-wise I don't know how this new one would compare to the old one. If you're looking to make the query fast, either with the query you provide or with a new query, probably the simplest way with the biggest impact is going to be to make sure you have indexes on char_id, vote_char_id, varc_char_id, vote_voter_id, and varc_voter_id in your tables. Thanks, Mac -- Mac Newbold [email protected] 801-694-6334 _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
