CODEWITHSUNDEEP
mysqlsqlsql-order-by
Alex P.
Alex P.

Reputation: 551

SQL ORDER BY performance

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

Upvotes: 4

Views: 1167

Answers (4)

Internet Engineer
Internet Engineer

Reputation: 2534

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.

Upvotes: 1

Aliostad
Aliostad

Reputation: 81660

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.

Upvotes: 9

JotaBe
JotaBe

Reputation: 39015

You have to alternatives to improve this: 1) create a new column with the needed value pre-calculated 1) create a second table that holds the videos primary key and the result of the calculation.

This could be a calculated column (in the firts case) or modify your app or add triggers that allow you to keep it in sync (you'd need to manually load it the firs time, and later let your program keep it updated)

If you use the second option your key could be composed of the finalRating plus the primary key of the videos table. This way your searches would be hugely improved.

Upvotes: 0

Churk
Churk

Reputation: 4637

Have you try moving you arithmetic of the order by into your select, and then order by the virtual column such as:

SELECT (col1+col2) AS a
FROM TABLE
ORDER BY a

Arithmetic on sort is expensive.

Upvotes: -1

Related Questions