Results 1 to 7 of 7
  1. #1

    Database indexing

    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...

  2. #2
    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.

  3. #3
    Join Date
    Dec 2002
    Location
    chennai , tamil nadu , IN
    Posts
    1,423
    Try creating indexes for the fields "online" and "posts"
    Chris, Developer/Programmer,
    Php laravel developers,
    Ruby on Rails developers,
    www.chrisranjana.com

  4. #4
    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?

  5. #5
    "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

  6. #6
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    What's the type of the field 'online'?

  7. #7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •