Keeping PHP pages fast even with millions of mySQL records.

[LeeTaxx0r]

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

[rsdl]

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

[datune]

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

[Jeff Lange]

and always, always remember, keep queries out of loops.

[DR_LaRRY_PEpPeR]

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.

[Mike]

What's indexing?

[DR_LaRRY_PEpPeR]

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.

[LeeTaxx0r]

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)

[Mike]

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

[Phil.Roberts]

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.