SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Keeping PHP pages fast even with millions of mySQL records.

    I may or may not be needing to worry about this anymore but I couldn't figure it out so I think it would be good to get some input from people.

    If you need to store a bunch of information about something, and you're storing millions of individual records, what's the best way of implementing this so the PHP pages don't take forever to process(forever being, half a second or so, since I would be expecting a lot of trafic).

    More specificly, I was going to make an item system in an online game where there could theoreticly be millions of different items, each having their own stats and they would all need to be stored in a way where I could quickly find which items belonged to the player logged in at the time. The problem was one big table obviously wouldn't work and the only other methods I thought up were arbitrarily dividing them into tables each holding 10,000 items. This seemed pretty kludgy though, and I'm sure there are some better ways.

    So, maybe someone who's had experience with large databases could gimme some insight? thanks

  2. #2
    pie??? PIE!!!! rsdl's Avatar
    Join Date
    May 2001
    Location
    Vancouver, BC Canada
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    really the biggest resource eater in php scripts are mysql queries.
    In order to get the best possible efficiency out of your php documents i'd recommend you take a look at methods to increase php efficiency such as using ' instead of "...etc, there are a lot of posts regarding script efficiency on this board so you might want to check that out.
    http://www.sitepointforums.com/showt...threadid=54074

    secondly i guess processing power. the more power you have, the faster php is processed.

    someone correct me if i'm wrong as i'd like to know myself

    -rsdl
    Last edited by rsdl; Mar 31, 2003 at 15:10.

  3. #3
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should not have any problems at all working with a MySQl DB that holds a few million records.

    My guess is that you will allow people to search for a specific item (from the game) and than you will display the result to them along with the stats.

    Use an index on the row you want to search, this will help decrease the processing time from MySQL (not PHP).

    Use a pagenavigator if you plan to display a lot of items on a page.

    What could also help is dividing the tables into categories, which will again speed up the search process. (of course this should make sense on your overall DB Design). Try to normalize as much as you can.

    I don't think PHP has anything to do with this at all, i think the key is your DB Design.

    Hope this helps

  4. #4
    "Of" != "Have" bronze trophy Jeff Lange's Avatar
    Join Date
    Jan 2003
    Location
    Calgary, Canada
    Posts
    2,063
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and always, always remember, keep queries out of loops.
    Who walks the stairs without a care
    It shoots so high in the sky.
    Bounce up and down just like a clown.
    Everyone knows its Slinky.

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    like datune said, it's a MySQL issue, not a PHP one. and there shouldn't be any problem with 10-100 million rows if the table and your queries are designed well (read: having and using good indexes).

    at the bottom of the coding tips post that rsdl linked to, there's some links to the MySQL manual about how indexes are used etc.

    using a database without knowing about indexing is like trying to drive a car without knowing about the accelerator -- it won't go very fast.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  6. #6
    ********* Genius Mike's Avatar
    Join Date
    Apr 2001
    Location
    Canada
    Posts
    5,458
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What's indexing?

    Mike
    It's not who I am underneath, but what I do that defines me.

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    serious question or joke? i don't know. if you don't know, check out the links about it from the manual at the bottom of my tips post like i said above.

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow. thanks! I seriously didn't know about indexing...lol

    Let's hope this solves the problems I was having(along with me re-designing my databse, lol)

  9. #9
    ********* Genius Mike's Avatar
    Join Date
    Apr 2001
    Location
    Canada
    Posts
    5,458
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I was joking, but I actually did learn something from that link

    Jeese, I haven't learnt this much in PHP since.... Matt was a regular here last year
    Mike
    It's not who I am underneath, but what I do that defines me.

  10. #10
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unless the data is updated on a regular basis, perhaps you should look into caching the data rather than re-querying it on every page view.


Bookmarks

Posting Permissions

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