How to improve perfomance of my voting system?

I've a website with a voting system (like/dislike).

The application has been developed by another developer, and now the website is getting bigger and bigger and performance is serious consideration.

i've the following table :

CREATE TABLE `vote` (
  `id` int(11) NOT NULL auto_increment,
  `article_id` int(11) NOT NULL,
  `token` varchar(64) collate utf8_unicode_ci NOT NULL,
  `type` int(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `article_id` (`article_id`)
) ENGINE=InnoDB;

The token column is used to identify each user/vote/date it is an unique token which is part of a user fingerprint to allow them to vote once and change their vote type.

One of the most slow query is the following:

SELECT count(*) AS `nb` FROM `vote` WHERE (token = '00123456789012345678901234567890');

It sometimes takes almost 10seconds to return when the server doesn't shutdown.

I can't use a cache he开发者_运维技巧re, because I need to check in a real time to allow or not the vote and increment the count.

I cannot change much application logic because it relies on too much dependancies used everywhere in the application (it was badly designed).

So I'm looking for options to improve, even a few, performance.

Edit: I've an index on token column

there are ~2,000,000 rows and all token are almost unique

EDIT:

I ran a benchmark with all your advises :

Top average queries
1. SELECT COUNT(*) AS nb FROM `vote` WHERE (`token` = '%s') completed in 2.19790604115 sec
2. SELECT COUNT(`id`) AS nb FROM `vote` WHERE (`token` = '%s') completed in 2.28792096376 sec 
3. SELECT COUNT(`id`) AS nb FROM `vote` WHERE (`token` = '%s') GROUP BY `token` completed in 2.3732401371 sec
4. SELECT COUNT(*) AS nb FROM `vote` WHERE (`token` = '%s') GROUP BY `token` completed in 2.57634830475 sec 

Sometimes the third query is the quickest but sometimes it's the worst.

I ran it 10 times where each query is run 20 times

I ran this benchmark WITHOUT any INDEXES (except one on id)

That's weird, I though the COUNT(id) would have speed up a bit the query.

You should look at indexing the token column, if it isn't already indexed.

It sounds like you should create an table that stores the summed data. This way the query doesn't need to do a full count each time but just a count from the last time it was summed. (Depending on your full system and if rows are never deleted you could have a table very similar to the follow)

CREATE TABLE `voteCounts` (
  `token` varchar(64) collate utf8_unicode_ci NOT NULL PRIMARY KEY,
  `count` int
) ENGINE=InnoDB;

Then when you insert a row into vote you can also call

UPDATE voteCounts
set `count` = `count` +1
WHERE
token = '012345' ;

In general, you should add indexes for columns in large tables that are used in where clauses of queries that are run often. In your example query, you'd need one on the token column. It looks like you are using MySQL database, so here's the important part of the create table statement for that database:

CREATE TABLE `vote` (
..
  token varchar(64) collate utf8_unicode_ci NOT NULL,
  index token_ind (token),
..
) ENGINE=InnoDB;

I havent really paid too much attention to your current implementation but the following method I use for 99.99% of voting systems is extremely performant:

Results:

mysql> select * from article;
+------------+-----------+-----------+-------------+--------+
| article_id | title     | num_votes | total_score | rating |
+------------+-----------+-----------+-------------+--------+
|          1 | article 1 |         5 |          15 |   3.00 |
|          2 | article 2 |         3 |           7 |   2.33 |
|          3 | article 3 |         2 |           6 |   3.00 |
+------------+-----------+-----------+-------------+--------+
3 rows in set (0.00 sec)

mysql> select * from article_vote;
+------------+---------+-------+
| article_id | user_id | score |
+------------+---------+-------+
|          1 |       1 |     5 |
|          1 |       2 |     4 |
|          1 |       3 |     3 |
|          1 |       4 |     2 |
|          1 |       5 |     1 |
|          2 |       1 |     2 |
|          2 |       2 |     1 |
|          2 |       3 |     4 |
|          3 |       1 |     4 |
|          3 |       5 |     2 |
+------------+---------+-------+
10 rows in set (0.00 sec)

Full script:

drop table if exists article;
create table article
(
article_id int unsigned not null auto_increment primary key,
title varchar(255) not null,
num_votes int unsigned not null default 0,
total_score int unsigned not null default 0,
rating decimal(8,2) not null default 0
)
engine = innodb;

drop table if exists article_vote;
create table article_vote
(
article_id int unsigned not null,
user_id int unsigned not null,
score tinyint unsigned not null default 0,
primary key (article_id, user_id)
)
engine=innodb;

delimiter #

create trigger article_vote_after_ins_trig after insert on article_vote
for each row
begin
 update article set 
    num_votes = num_votes + 1,
    total_score = total_score + new.score,
    rating = total_score / num_votes  
 where 
    article_id = new.article_id;
end#

delimiter ;

insert into article (title) values ('article 1'),('article 2'), ('article 3');

insert into article_vote (article_id, user_id, score) values
(1,1,5),(1,2,4),(1,3,3),(1,4,2),(1,5,1),
(2,1,2),(2,2,1),(2,3,4),
(3,1,4),(3,5,2);

select * from article;
select * from article_vote;

Hope it helps :)