Database indexing

Portal Home > Knowledgebase > Articles Database > Database indexing

Posted by mdburton, 01-07-2008, 10:35 AM
Not sure if this is a correct forum, or if any forum is appropriate on this site 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...
Posted by mdburton, 01-07-2008, 10:54 AM
also this is what i used to learn indexing ; ) http://www.databasejournal.com/featu...0897_1382791_1 I think that is the article, but I'm not sure. To test queries I log into mysql via ssh.
Posted by chrisranjana, 01-07-2008, 11:50 AM
Try creating indexes for the fields "online" and "posts"
Posted by mdburton, 01-07-2008, 02:47 PM
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?
Posted by mdburton, 01-07-2008, 02:55 PM
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
Posted by Steve_Arm, 01-07-2008, 04:19 PM
What's the type of the field 'online'?
Posted by mdburton, 01-07-2008, 06:39 PM
time is a int(11) and i got the first one working, but i cant find out how to do...