SQL ORDER BY performance

Question

I have a table with more than 1 million records. The problem is that the query takes too much times, like 5 minutes. The "ORDER BY" is my problem, but i need the expression in the query order by to get most popular videos. And because of the expression i can't create an index on it.

How can i resolve this problem?
Thx.

SELECT DISTINCT 
`v`.`id`,`v`.`url`, `v`.`title`, `v`.`hits`, `v`.`created`, ROUND((r.likes*100)/(r.likes+r.dislikes),0) AS `vote` 
FROM 
`videos` AS `v` 
INNER JOIN 
`votes` AS `r` ON v.id = r.id_video 
ORDER BY 
(v.hits+((r.likes-r.dislikes)*(r.likes-r.dislikes))/2*v.hits)/DATEDIFF(NOW(),v.created) DESC

Answer 1

Does the most popular have to be calculated everytime? I doubt if the answer is yes. Some operations will take a long time to run no matter how efficient your query is.

Also bear in mind you have 1 million now, you might have 10 million in the next few months. So the query might work now but not in a month, the solution needs to be scalable.

I would make a job to run every couple of hours to calculate and store this information on a different table. This might not be the answer you are looking for but I just had to say it.

Answer 2

What I have done in the past is to create a voting system based on Integers. Nothing will outperform integers.

The voting system table has 2 Columns:

ProductID

VoteCount (INT)

The votecount stores all the votes that are submitted.

Like = +1

Unlike = -1

Create an Index in the vote table based on ID.