LIMIT

[Anon]

I am showing a ranked user list on my page that 1000s of people run on a daily basis.

there are like 30,000 users.

right now I select * and then do an ORDER BY on their overall value, and then to show them 50 at a time i use LIMIT.

would it be better to just assign a rank to a user every hour or so, and then just do 50 selects (like, get rank 1, get rank 2...)

which one uses less memory and CPU? I have to streamline the site because the server is getting tons of traffic.

send replies to [e-mail]

[Anon]

First, you post here, so you read the replies here. We don't email to you personally. (does that sound grumpy enough? :-))

Second, be carefull about how you optimize your queries.
While a query like "select rank 2" might be quicker than using one big query, you will in fact do 49 queries more. That means your 50 queries must each take less than 1/50th of the time the big query takes in order to be faster.

Do you have a index on the fields that you select on, or are the the primary key?
If not, then 50 queries will be quite a bit slower than one big query, because the database server will have to go through each and every record in the table to get the answers. As you can imagine, doing that 1 time or 50 times is quite a difference.

If speed is a big issue, and your rankings don't change by the minute (or you don't care about displaying realtime data), why not
store the results you want to display in a seperate table?
This table could contain the output of your big query along with an auto_increment primary key field to count the rows.
Then if you want results 50-99, you can select the rows from that table;
select * from new_table where ID<100 and ID>49;
which requires near zero work from the databse in terms of searching.

[Anon]

what is the php code to store the result into a new table?

the only keys i use is 'id' and 'overall_value' so the new table would require those and an auto-increment rank field. I just don't know the code to do that.

[vincent_e]

Just create a table like your original table and:

insert into new_table select * from old_table;

If you add an auto_increment rank field, you will have to change your quiry so that a 'zero' is selected for that new column:

insert into new_table select *,0 from old_table;

But ofcourse it would be best if you name each field seperately.

[Anon]

ok... so my old table is wallet and my new table will be ranked_userlist

so this would work

INSERT INTO ranked_userlist SELECT id,overall_value,0 FROM wallet ORDER BY overall_value DESC

so then ranked_userlist would have 3 fields, id, overall_value, and the rank field.

but I would want to do this every 30 minutes or hour... so if I removed all of the rows, then the next time I did it, would the autoincrement would reset to 0? or would i have to totally remove the table and recreate it each time?