Calculating rank of item based on votes in SQL

The example you gave
Would it use alot of cyles to calculate the rank? If say there were 100k rows?

I don't mean to interrupt, but...

> Would it use alot of cyles to calculate the rank? If say there were 100k rows?

Yes it certainly would, as for each `PictureId` a ranking query is executed. This is extremely demanding on resources.

As Raynard7 suggested, it would be far better and faster to calculate the rank in your script.

Hi thanks leannonn.
How would you suggest i script that?

Thanks :)
Feds

> How would you suggest i script that?

Depends on exactly what you're trying to do.

If you're displaying a sequential list of pictures then use one query to get the picture list _without_ the rank and a separate query to get the rank just for the first item and manually increment the value in the script for all the items in your list. Same method if you're displaying info for a single picture.

Thanks,

I'll explain what im trying to achive. We have pictures which are  tagged and people can vote on.
I want to be able to display their rank on a search results page...as in how popular they are.
So even if a search result only returns 4 results...i would also like to work out their rank based on the number of votes they have...compared to EVERY other picture in the list (the actul pictures are stored eles where but the vote count and picture id are kep in the table i mentioned in my question)

Would it make senes to use the query :
select yt.Pictureid, yt.votes, (select count(*) from yourTable as yt1 where yt1.Votes > yt.votes) + 1 from yourTable as yt

But only do it say once every half hour and store them in a rank table with a coresponding picture id.
Almost like chacehing it?

Feds

Yes, that would work. You would still have to make sure the correct indexes are in place and are being used. You'll need every bit of speed with 100k rows...

Would it be even faster to rank the votes by 'oder by votes DESC' and work out its position based on that?

> Would it be even faster to rank the votes by 'oder by votes DESC' and work out its position based on that?

You mean for the half-hour query which updates all the 100k rows? You could try something like this:
---
CREATE TABLE ranksTable(
  pictureID integer NOT NULL primary key,
  rank integer NOT NULL
)
SELECT
  yT.pictureID,
  @r:=@r+1
FROM
  yourTable yt
ORDER BY
  ytvotes DESC
---

This should also be as quick as it can be for your table size.

Thanks!!
But i do have a few question about that.
Some people upload items all the time (hence how i have 100k rows already(this is a uni internal website)

1) By running that script every half hour how do i deal with images that are uploaded and voted on in the mean time...or should i just set their rank to N/A untill the next time the script is run
2) Will this script override the previous table called rankstable...everytime...or do i need to change the name or? ( i dont want to site do crash everytime i run the script..while its doing it calulations)

But thanks so much for all your help so far! This is my first time working on a site like this/
Feds

> 1) By running that script every half hour how do i deal with images that are uploaded and voted on
> in the mean time...or should i just set their rank to N/A untill the next time the script is run

Yes, you could do that as the delay should be acceptable. You can display the accurate `votes` data at any time and the `rank` is updated periodically. You can also measure the execution time of the CREATE TABLE query and if it executes fast, you can always reduce the interval (15min, 10min...)

> 2) Will this script override the previous table called rankstable...everytime...or do i need to change
>  the name or? ( i dont want to site do crash everytime i run the script..while its doing it calulations)

You can create the table manually the first time:
---
CREATE TABLE ranksTable(
  pictureID integer NOT NULL primary key,
  rank integer NOT NULL
);
---

Then on each run you can execute this:
---
REPLACE INTO ranksTable(pictureID, rank)
SELECT
  yT.pictureID,
  @r:=@r+1
FROM
  yourTable yt
ORDER BY
  ytvotes DESC
---

This will update the old data and insert any new data (for newly uploaded pictures).

Hey Leannon.
I am doing what you suggested, but im getting the error:
Data truncated for column 'rank' at row 1

This is the code: (with my table and cols in it)

REPLACE INTO ranksTable(pictureID, rank)
SELECT
  yT.PicId,
  @r:=@r+1
FROM
  picture_count yt
ORDER BY
  yt.VoteCount DESC

-----------------------------------------

Try issuing the commands in this order. The variable does not reset in the same session:
---
SET @r:=0;

REPLACE INTO ranksTable(pictureID, rank)
SELECT
  yT.PicId,
  @r:=@r+1
FROM
  picture_count yt
ORDER BY
  yt.VoteCount DESC;
---

:(
I'm still getting the same error. Im sure im doing something wrong but i dont know what.
I copied and pasted
SET @r:=0;

REPLACE INTO ranksTable(pictureID, rank)
SELECT
  yT.PicId,
  @r:=@r+1
FROM
  picture_count yt
ORDER BY
  yt.VoteCount DESC;

Into MySql Query Browser and im getting the same error :(

Thanks for your help
Feds

WONDERFUL!!!
So this only will update rows that need changing...like new itmes etc.
This works really well..thanks so much
Feds

Oh just one question...how long would it take..or the amount of resrouces with the 100k pic...i know it depends on the actul server and what eles its doing...but what would you consider reasonable

This should be very, very fast - below 2 seconds.This is from my test tables:
---
mysql> SELECT count(*) FROM picture_count;
+----------+
| count(*) |
+----------+
|   101024 |
+----------+
1 row in set (0.00 sec)

mysql> REPLACE INTO ranksTable(pictureID, rank)
    -> SELECT
    ->   yT.PicId,
    ->   @r:=@r+1 as rank
    -> FROM
    ->   (SELECT @r:=0) r, picture_count yt
    -> ORDER BY
    ->   yt.VoteCount DESC;
Query OK, 202048 rows affected (1.83 sec)
Records: 101024  Duplicates: 101024  Warnings: 0
---