MySQL Join Issue

Question

I'm working on a political application for a client, and I've got two database tables. One has all the registered voters in a certain city, and the other has all the votes for each registered voter. Combined, these two tables number well over 7 million records. This site is coded on CakePHP.

I'm already narrowing the results by a number of criteria, but I need to filter it also based on the percentage of elections that a given voter has voted in since they registered. I have all the votes, the year they registered, and that there are 3 elections every 4 years. I've tried doing a subquery to filter the results, but it took far too long. It took 10 minutes to return 10 records. I have to do this in a join some way, but I'm not at all versed in joins.

This is basically what I need to do:

  SELECT * FROM voters 
     WHERE (number of votes voter has) >= (((year(now())-(registration_year) ) * 3/4)
     * (percentage needed))

All of that is pretty straight-forward. The trick is counting the votes the voter has from the votes database. Any ideas?

score 0 · Accepted Answer · answered Aug 12 '11 at 14:43

0

Either create another table, or extend your first table (the one containing voter information, but not their votes) with two columns -- #votes and registrationAge. Then you can update this table by scanning the 'votes' table once -- everytime you encounter a vote, just increase the count.

answered Aug 12 '11 at 14:43

michel-slm

9,438
3
32
31

that thought had never actually occured to me. That way, I actually don't need the voter history table. It's only for getting the number of elections that they've voted in, so I can just add the number of votes to the table. Thanks! – LukeWaggoner Aug 13 '11 at 06:39

score 0 · Answer 2 · answered Aug 12 '11 at 15:31

0

I wouldn't try to calculate this as part of your query

In a case where this info will only change 3 times in four years, I'd add the voted % field to the voter table and calculate it once after each election. Then you can simply filter by the field.

answered Aug 12 '11 at 15:31

Jaydee

4,138
1
19
20

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

you can add a vote_count field to voters table and do a update count on that. You might want to do it in straight sql query: Aggregate function in an SQL update query?

Also, I'm not sure if mysql smart enough to optimize this, but don't use year(now()): you can either get that value in PHP, or just hard code it each time you run (you probably don't need to run it too often).

edited May 23 '17 at 11:55

Community

1
1

answered Aug 12 '11 at 15:36

Anh Pham

5,431
3
23
27

score 0 · Answer 4 · answered Aug 12 '11 at 15:37

0

How about this:

SELECT voters.* FROM voters
     LEFT JOIN (SELECT COUNT(voterid) AS votes,voterid AS id FROM votes) AS a
         ON voters.id = a.id
     WHERE a.votes >= (((year(now())-(voters.registration_year) ) * 3/4) * percentage

answered Aug 12 '11 at 15:37

AgDude

1,167
1
10
27

score 0 · Answer 5 · answered Aug 13 '11 at 02:40

0

I would recomend to create a view, then model your vie to fetch the data

answered Aug 13 '11 at 02:40

netors

36
1
1

MySQL Join Issue

5 Answers5