Database Designing for Vote Buttons

Question

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 :

voteId-----AUTO_INCREMENT with PRIMARY KEY.
mediaId----The Media for which user gives an up/down vote.
userId-----The User who Voted.
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).

The PHP framework you use is (or should be) completely unrelated to the database structure. And that goes even more for the fact that you use JQuery. You could write a voting desktop application and still need the same database. Don't let those things clutter your decision-making. — GolezTrol, Sep 10 '12 at 11:18
No i am not. Not much familiar with it Wayne. I never tried much DB before. — Vijay Sarin, Sep 10 '12 at 11:19
It would seem risky at best, and outright irresponsible at worst, to offer services to a "client" in which you have next to no experience. — Kerrek SB, Sep 10 '12 at 11:20
When one votes , you are inserting into database , right ? is that happening very slowly? — MD. Sahib Bin Mahboob, Sep 10 '12 at 11:23
@MD.SahibBinMahboob Yes, its slow. But only when there are around 15K records. — Vijay Sarin, Sep 10 '12 at 11:24
but post a SHOW CREATE TABLE so we can see your indexes, engine, etc — Nin, Sep 10 '12 at 11:42
Actually i am using Citrix and the DB is inside. That is why i typed in the fields instead of the queries. I cant copy anything from it. But let me check my Indexes. — Vijay Sarin, Sep 10 '12 at 12:10

score 1 · Answer 1 · edited Dec 14 '14 at 22:15

1

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

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.
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.

edited Dec 14 '14 at 22:15

Paritosh Piplewar

7,982
5
26
41

answered Sep 10 '12 at 11:41

Nin

2,960
21
30

Its InnoDB. Check my Comment to the Question you asked on top – Vijay Sarin Sep 10 '12 at 12:11
here is it : Actually i am using Citrix and the DB is inside. That is why i typed in the fields instead of the queries. I cant copy anything from it. But let me check my Indexes. – Vijay Sarin Sep 10 '12 at 12:11
Got it buddy, no index was actually defined...! I tried SHOW CREATE TABLE and couldn't find one there. Also, i was using InnoDB Only. – Vijay Sarin Sep 10 '12 at 12:29
To be Frank, i was not much aware of MySQL and DB Operations. I tried Index Just now. Its working fine.... – Vijay Sarin Sep 10 '12 at 12:30
Its working fine now. Now i am started to work on Lucene with Zend to make it more faster. – Vijay Sarin Sep 11 '12 at 05:21

MD. Sahib Bin Mahboob · Accepted Answer · 2012-09-10T12:21:52.553

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:

Some resources about MySql Database optimizations :

Thanks for the info. Let me try this. – Vijay Sarin Sep 10 '12 at 12:13 — Vijay Sarin, Sep 10 '12 at 12:13

score 0 · Answer 3 · answered Sep 10 '12 at 11:29

0

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.

answered Sep 10 '12 at 11:29

JvdBerg

21,777
8
38
55

Yes, any other way to store this Data...? – Vijay Sarin Sep 10 '12 at 11:30
i have restricted multiple voting and anonymous voting – Vijay Sarin Sep 10 '12 at 11:34

Database Designing for Vote Buttons

3 Answers3

Linked