PHP/mySQL performance.
Moderator: General Moderators
PHP/mySQL performance.
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.
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.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: PHP/mySQL performance.
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?spedula wrote:When will I start seeing significant performance issues, like 1,000's or 10,000's
“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
Re: PHP/mySQL performance.
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.
Re: PHP/mySQL performance.
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)
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)
Re: PHP/mySQL performance.
Right now, every page load has 6 query's to different tables and then loop that has a variable amount of queries, as such:
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?
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];
}
}
}How can I change that to be just one query, and then base my loop around the returned resource of that query?
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: PHP/mySQL performance.
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.spedula wrote:So I don't really see what your questions was getting at.
“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.
$keyid[$i] seems to vary, you make use of in like keyId in (every keys from $keyid array)Code: Select all
$query = mysql_query("SELECT elementValue FROM sitesettings WHERE storeid='".$storeid."' AND keyId='".$keyid[$i]."' AND pageid='".$pageid."'");
Re: PHP/mySQL performance.
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.
Re: PHP/mySQL performance.
I have no idea what you're trying to convey. I'm new to PHP (~4 months) and even newer to SQL.$keyid[$i] seems to vary, you make use of in like keyId in (every keys from $keyid array)
Can you please explain that in more detail?
Thanks for the tip. I'll look into profiling when I get a chance.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.