What is the limit of PHP / MySQL?

[MichaelCapone]

Hi everyone.

I am in the very early stages of designing a textual, statistical baseball simulation game. After watching Halls' videos using WAMP, I plan to use that for the development.

One question, though. I am sitting down trying to plan out my Database. I am going to have a players table in my Database which will house every position player created throughout the game. I plan on having 12 teams (25 players each), a 15-man pool of reserves for each team (kind of like a waterdowned Triple-A affiliate), and each year 60 new players will be created for a draft. I plan on allowing the user to play 40 seasons. I will not be deleting retired players from the Database. I will keep them and just mark them as inactive so it is easier to run top-10 lists, records, and such.

So to do some quick math...at the end of the 40th season, I will have about 2,880 players in the players table...if only one person plays my game. Let's say I have 100 users. Now we have 288,000 records in the player tables.

My question is, what kind of limits are there on PHP / MySQL? Is there a physical limit of records per table? If I do reach close to 300,000 records, how bad will performance be?

Any thoughts before I really commit to this game? I am pretty raw when it comes to this, but have a good idea how to write the PHP. I just don't kow much about performance gains and what maximum capacity is. Thanks!

[Jackolantern]

MySQL can handle huge tables, or it would not be considered an enterprise-ready RDBMS. However, as the data grows larger and larger, searches and other functions will become much more taxing on the server.

What you could potentially consider doing is creating a system to retire and hold older stats. Only the current season's stats would likely need constant additions and reads, or would change. Then take the older stats and summarize them in any way you will need them summarized, and keep those summaries in a small, concise database table that will read fast. That way you can keep all of the old stats without throwing anything away, but use an intermediate table with all of the info you need in it. If you need the old seasons' data summarized in any different way, you can always simply run the new summary on the old data, and add it to the summaries table.

That is the way large, unchanging amounts of data are often handled: through archiving, and essentially caching the results of common queries.

[MichaelCapone]

Thank you for the reply. I had not thought of that. I will defintely build that in to my design. Thank you.

[Jackolantern]

No problem

[herrydevid]

I guess PHP is one of the best making websites programming language and mostly wide known. I don't think it has any limits which is fully features included.

[hallsofvallhalla]

Think of this site. 40,000 posts with all that text, setting, avatars, users, ect... All PHP and Mysql

[xcalpro]

I was directed to this article http://me.veekun.com/blog/2012/04/09/ph ... ad-design/ which points out the shortcomings of PHP and after reading it, I am considering using another language for my web applications. What would be an alternative? Python, Ruby, or Java?

[Jackolantern]

Don't listen to the contrarian (someone who takes the opposite position on a popular topic just to be different) lol. PHP can be misused just like any other language. But its history, and the millions of websites powered by it speak for itself that PHP is an excellent and powerful language. Don't get sucked into the hunt for the "perfect language" because you will find articles like this for every language under the sun. There are going to be people who can write long articles on how every language stinks and shouldn't be used.

The results of others here and elsewhere prove the language is capable of just about anything

[xcalpro]

Yeah I guess it all comes down to how you use it. I would hate to have to learn a new way of doing things only to find out its no better than the old way.

[Jackolantern]

The main problem to PHP is that most beginners pick it up and write old-style "all-in-one-pot" scripts, that have HTML, PHP and Javascript all mixed into the same thing. This is a nightmare to maintain and extend down the line because the functionality, semantic structure and data access are all jumbled together. However, this can easily be fixed with one of the many MVC frameworks out there. My personal favorite is CodeIgniter because it is lightweight, simple to use and easy to learn. You can use as much or as little of it as you see fit, it doesn't enforce any particular coding styles outside of some OOP, it has a massive following and a large number of plugins.

EDIT: Ok, I read a bit more into the article. It has some points, but heck...every language has bad parts. The most widely used language in the world is Javascript, and it is one of the most nonsensical, dangerous languages in usage today. Each language these PHP-bashers claim is far superior, such as Ruby or Python, also have lists just like these compiled about them. Any language that is #1 in usage in their field is going to take a lot of hits by people who use something else and want their language to be #1. Java has had literally thousands of these types of lists written about it.

Some of the article's points had merit, but about 75% of it seemed like the author was just going through the PHP config file and listing things that he felt shouldn't be in the config file because it can create differently acting platforms. Well...yeah...that is typically what config files do lol.

Definitely don't dismiss PHP because of this article. 95% of PHP coders will never have an issue with the vast majority of things on this list. PHP's heritage as one of the very first solely server-side web languages means it inherited a relatively small body. You can use one of the many tools for it to add things that are built on at the hip in other languages, such as an MVC pattern, object relational mapping, etc., or you can just use it vanilla-style. That is one of the reasons why it is so excellent for new programmers. Frameworks like Ruby on Rails will scare away people with little programming experience before they even begin because of their depth and their enforcement of professional web developer work flows.