PHP/mySQL performance.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

PHP/mySQL performance.

Post by spedula »

I'm coding an app, in short it's a template engine/CMS, where every user will have over 200 rows in one table and several tables with similar amounts. I've come up with a solution that will create a new DB based on how many users are assigned to it. That part is functioning 100% fine.

Just for clarification purposes:
My users will be called clients
Clients users will be called customers

Each client can potentially be serving a multitude of customers. Clients site will be sending requests for mysql data for each and every customer. This means that the script has to look through 1000's of rows of data just for that 1 customer to get the info that they requested, and there can be a very large number of simultaneous customer. I've already put in place a way to reduce the amount of rows per table per client by doing the DB split.

My question is, how many rows is too many? I know it's a little vague but generally speaking. When will I start seeing significant performance issues, like 1,000's or 10,000's rows? More?

Please let me know if you need more info.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PHP/mySQL performance.

Post by social_experiment »

spedula wrote:When will I start seeing significant performance issues, like 1,000's or 10,000's
You are more likely to see performance issues when you start reaching the 10000 row mark. Are you refering to performance issues on the side of the CMS or to server performance?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: PHP/mySQL performance.

Post by spedula »

Well the server performance will affect the CMS performance, will it not? So I don't really see what your questions was getting at. Please explain if I'm not understanding correctly.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: PHP/mySQL performance.

Post by mikosiko »

In general, any database (MYSQL Included) can scale well to millions of records assuming that it is well designed, indexed and queried properly.

Only after you check completely your indexing strategy, your query design (and performance using explain plans) and being sure that your performance issues are coming from the DB, only then worth to start looking into techniques such as sharding/partitioning/caching and in some extremes or very particular cases look into alternative database solutions. With a few 1000's rows performance is no a big deal at least something is really wrong in the design (Db, Indexes or querys)
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: PHP/mySQL performance.

Post by spedula »

Right now, every page load has 6 query's to different tables and then loop that has a variable amount of queries, as such:

Code: Select all

// gets all site style values and script style values
$r = count($keyid);
$sc = count($script);
$values[] = '';
$scriptValue[] = '';
for ($i = 0; $i < $r; $i++){
    $query = mysql_query("SELECT elementValue FROM sitesettings WHERE storeid='".$storeid."' AND keyId='".$keyid[$i]."' AND pageid='".$pageid."'");
    $rowdata = mysql_fetch_array($query);
    $values[$i] = $rowdata[elementValue];
	for ($s = 0; $s < $sc; $s++){
		if ($script[$s] == $keyid[$i]){
			$scriptValue[$s] = $rowdata[elementValue];
		}
	}
}
Which can be up to 25 or so loops.

How can I change that to be just one query, and then base my loop around the returned resource of that query?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PHP/mySQL performance.

Post by social_experiment »

spedula wrote:So I don't really see what your questions was getting at.
If you designed the CMS badly the performance problems will be here, if you didn't then you only have to worry about server performance (regarding your row total). mikosiko covered it pretty well in his reply.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
kalpesh.mahida
Forum Commoner
Posts: 36
Joined: Wed Oct 06, 2010 7:09 am

Re: PHP/mySQL performance.

Post by kalpesh.mahida »

Code: Select all

$query = mysql_query("SELECT elementValue FROM sitesettings WHERE storeid='".$storeid."' AND keyId='".$keyid[$i]."' AND pageid='".$pageid."'");
$keyid[$i] seems to vary, you make use of in like keyId in (every keys from $keyid array)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: PHP/mySQL performance.

Post by josh »

Mysql can handle billions of rows. Performance issues will happen due to sub-optimal coding. For example, issuing a bunch of small queries instead of one large one, or vice versa. Or not indexing a table, or using joins instead of sub-queries. Read up on mysql profiling rather than worry about 200 vs 2,000 rows. Row count is not the sole influence of performance.. Your coding skills, and your hardware are.
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: PHP/mySQL performance.

Post by spedula »

$keyid[$i] seems to vary, you make use of in like keyId in (every keys from $keyid array)
I have no idea what you're trying to convey. I'm new to PHP (~4 months) and even newer to SQL.

Can you please explain that in more detail?
Mysql can handle billions of rows. Performance issues will happen due to sub-optimal coding. For example, issuing a bunch of small queries instead of one large one, or vice versa. Or not indexing a table, or using joins instead of sub-queries. Read up on mysql profiling rather than worry about 200 vs 2,000 rows. Row count is not the sole influence of performance.. Your coding skills, and your hardware are.
Thanks for the tip. I'll look into profiling when I get a chance.
Post Reply