Not sure if this is a correct forum, or if any forum is appropriate on this site :stickout:


So if anyone knows a better site where I can get some answers let me know.


Ok so I have a query that

$get = mysql_query("Select * from user WHERE online>$check order by posts DESC limit $offset, $limit");

This is how it is basically...

then I have a

while ($table = mysql_fetch_array($get))
{
displays user information of those that have been active in the past 10 mins, seperated by pages...
}

So how would I index this? I've read about indexing and it is easy to index things like id and stuff, but online field is just time() that is updated every time a user triggers it

Is it possible to make a index based upon *time()-600*? Where it basically seperates users from online users, and when I do order by posts which isn't indexed is that going to make the query even worse?

Right now the amount of users I have is 30k and will climb to 3,000,000 at the end of the month. So I really need to do something to optimize this page or else it is destined to have problems...

also this is what i used to learn indexing ; )

http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1

I think that is the article, but I'm not sure. To test queries I log into mysql via ssh.

Try creating indexes for the fields "online" and "posts"

mysql> EXPLAIN SELECT * from users WHERE online>1199730595 order by posts DESC limit 0,25;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| 1 | SIMPLE | users | range | online | online | 4 | NULL | 198 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
1 row in set (1.12 sec)


nice, it was at 27,000 rows before I have another problem tho also which ill post below this one...

edit: also what is a good reason for this to be a slow query? server under too much of a load?

"Select * from user order by posts DESC limit 0, 25";


So I want to display the top 25 users that have the most posts....

when i run this query right now it looks at 27,000+ rows (every one)

is there a way to have the database constantly sort by posts DESC so it doesnt have to examine every row?

Basically, I want a ranking page, that splits up all the users onto 25 users per page arranged with the person that has the most posts at #1

but right now when you do page one it scans the whole database, then page 2 it scans the whole database

ive been stuck on this one for awhile, ive been able to figure out other things but this one is the worst query i have now


this is what my status says: Threads: 11 Questions: 4387830 Slow queries: 267 Opens: 128682 Flush tables: 1 Open tables: 64 Queries per second avg: 48.260

imma lookup what each one means exactly

What's the type of the field 'online'?

time is a int(11)

and i got the first one working, but i cant find out how to do...

"Select * from user order by posts DESC limit 0, 25";


So I want to display the top 25 users that have the most posts....

when i run this query right now it looks at 27,000+ rows (every one)

is there a way to have the database constantly sort by posts DESC so it doesnt have to examine every row?

Basically, I want a ranking page, that splits up all the users onto 25 users per page arranged with the person that has the most posts at #1

but right now when you do page one it scans the whole database, then page 2 it scans the whole database