表决按钮的数据库设计 [英] Database Designing for Vote Buttons

问题描述

我正在一个项目，我有投票选项向上和向下类似于StackOverFlow。我在DB Designing没有多少经验，因此我起来了以下问题，

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. ----用户向上/向下投票的媒体。


3. userId -----投票的用户。


4. voteMode --- 1表示Up Vote，0表示Down Vote。这是一个整数字段。

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.

在这种情况下，如果我有100个用户和100个媒体，这个表。

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.

任何人都可以向我建议一个更好的模型，以避免使用这个巨大的表格。

我使用 jQuery.ajax发布我的投票给服务器。此外，该项目基于 PHP和Zend Framework 1.11 。所以当我点击UP图标，它需要一些时间来回应。 Mozilla用来崩溃某些时候。 我通过带有大量垃圾记录（大约15000）的循环插入测试。

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

推荐答案

可以尝试这些表格模式的渐变：

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` ) ;

浏览 Mysql Index 了解更多关于索引的信息。

Go through Mysql Index to know more about indexing.

如果您使用< =http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html =nofollow> MyISAM存储引擎，那么我会建议你去 InnoDB存储引擎。 它可以帮助您决定应该使用哪个引擎

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. a href =http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html =nofollow> MySQL查询缓存


2. 在php中准备语句


3. COLUMNS分区

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

有关MySql数据库优化的一些资源：

Some resources about MySql Database optimizations :

1. MySQL调整


2. Mysql优化


3. 现实世界可扩展性MySQL 。

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

这篇关于表决按钮的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！