On Wed, Jul 20, 2011 at 12:24 PM, Mac Newbold <[email protected]> wrote:
> On Wed, Jul 20, 2011 at 9:59 AM, Wade Preston Shearer
> >
> > 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:
>
Why the split? Seems unnecessary. I would also go without the different
column names between "current" and "archive" tables.
> > The query above works and returns that information. Is that the best way
> to do that though?
>
Your original query pulled the charity name from both inner queries. There's
no need for those extra disk reads if you're only going to be using them at
the outer query, and only once per charity. Your DB is (probably) smart
enough to cache that, but still...
> 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;
>
Improving a bit on Mac's take, here's my own take.
SELECT COUNT(*) AS votes, c.char_name
FROM charities c JOIN
( SELECT v.vote_char_id AS char_id
FROM database.votes v
WHERE v.vote_voter_id=' . intval($_POST['voter_id']) . '
UNION ALL
SELECT va.varc_char_id AS char_id
FROM database.votes_archive va
WHERE va.varc_voter_id=' . intval($_POST['voter_id']) . '
) AS cv
ON c.char_id = cv.char_id
GROUP BY c.char_name
ORDER BY c.char_name
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
>
Agreed. Use EXPLAIN ANALYZE (if on PostgreSQL) on both to check.
> 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.
>
Agreed.
Roberto Mello
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
