Database Designing for Vote Buttons

问题:

I am working on a Project in which i have Voting Options Up and Down similar to StackOverFlow. I am not much experienced in DB Designing and thus i got up with the following issue,

First of all, here is my table structure for voting :

1. voteId-----AUTO_INCREMENT with PRIMARY KEY.
2. mediaId----The Media for which user gives an up/down vote.
3. userId-----The User who Voted.
4. voteMode---1 for Up Vote and 0 for Down Vote. This is an Integer Field.

In this case if i have 100 users and 100 medias, then i will have 100x100 records all total in this table.

The Problem arises here is the DB is getting up with a lot of records and the vote button is dead slow to react now. This is making my client unhappy and me in trouble.

Can anyone suggest me a better model to avoid this Huge Table?

I am using jQuery.ajax to post my vote to server. Also, the project is based on PHP and Zend Framework 1.11. So when i click on UP Icon, it will take some time to respond. Mozilla used to Crash certain times. I tested with inserting via a loop with lots of junk records (around 15000).

回答1:

You can try these up gradation of your table schema:

//All id's are now unsigned , As you do not need any sign 
ALTER TABLE `voting` 
CHANGE `voteid` `voteid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, 
CHANGE `mediaId` `mediaId` INT(11) UNSIGNED NOT NULL, 
CHANGE `userId` `userId` INT(11) UNSIGNED NOT NULL, 
//ENUM datatype as you need only 2 type of value
CHANGE `voteMode` `voteMode` ENUM('1' , '2') NOT NULL ; 

//Adding index , it will surely increase some speed
//Do **not use** index in **columns you do not need**
ALTER TABLE  `voting` ADD INDEX (  `mediaId` ,  `userId` ) ;

Go through Mysql Index to know more about indexing.

If you are using MyISAM Storage Engine , then i will suggest you to go for InnoDB Storage Engine. It may help you to decide about which engine you should use.

And some other hacks that may help you are:

1. MySQL Query Cache
2. Prepared Statements in php
3. COLUMNS Partitioning

Some resources about MySql Database optimizations :

1. MySQL Tuning
2. Mysql Optimization
3. Real World Scalability MySQL.

回答2:

OK, two things. 15k records is nothing so that can't hardly give a problem. I'm using tables with 150M rows and queries are still performing well under .005s

1. I'm suspecting you're using MyIsam and not InnoDB. With MyIsam, each insert (or update) locks the entire table. So while someone is voting, the tables is locked and others can't read from it. This might become a problem if you have thousands of users.

2. Make sure you have the right indexes. I'm not sure what queries are slow (and how slow!) but make sure you have an index on the columns you are searching for (probably mediaId).

If you want better advise, post the queries that are slow.

回答3:

If you want to keep track of what user has voted for x media, and every user votes, then your minimal data amount is users * media.

If you want to have less data, you have to make a concession. Perhaps let a user register and vote anonymously? Most users are not very happy if there personal preferences can be distilled from there voting behavior.