db-queries

[Bernd Kohler]

Hello,

some little questions relating LAMP (PHP 4.3.4, MySQL 3.x, Apache):
I'm trying to build a community website which needs many db-querys for lots
of concurrent users.
Therefore I'm always using:
$ergebnis = mysql_query ($sql) or die (mysql_error());
while ($row = mysql_fetch_array($ergebnis))
{
$myvar = $row['column_xy'];
}

Is there a limit for db-connections under MySQL3.x?
What happens if hundreds of users are accessing these pages simultaneously?
I heard MySQL breaks down if there are more than 200 users!?

Is there a better/more efficient way for the db-queries under PHP4?
How efficient would it be to write all possible db-results into
session-variables in order to keep the number of queries low?
What about performance using many session-variables?

Thank you very much in anticipation!
Bernd

[Mike Willbanks]

Bernd,

some little questions relating LAMP (PHP 4.3.4, MySQL 3.x, Apache):
I'm trying to build a community website which needs many db-querys for lots
of concurrent users.
Therefore I'm always using:
$ergebnis = mysql_query ($sql) or die (mysql_error());
while ($row = mysql_fetch_array($ergebnis))
{
$myvar = $row['column_xy'];
}

Is there a limit for db-connections under MySQL3.x?
What happens if hundreds of users are accessing these pages simultaneously?
I heard MySQL breaks down if there are more than 200 users!?

First off 3.x is not the MySQL installation you should be using. 4.0 or
4.1 you should be using now. Also your PHP installation is dated where
PHP is at version 4.3.11.

Now as for users, I have never had MySQL break down. I also have 50
websites that run off of one database with one administration panel.
These sites get fairly heavy traffic.

Now what you should pretty much do is Cache your queries so you are not
always querying the database. Only query when you need to. Make use of
JOIN statements, so you are not constantly querying for data that could
be in one query. Sometimes these JOINs can get a bit messy because some
of them are INNER JOINS some are LEFT OUTER JOINS etc.. And you end up
with a paragraph long query. But simply, it runs much faster.

Make sure you have index's on certain id's and things you will be
looking up regularly such as usernames and passwords, this allows you to
do an index query. These are normally very quick.

Also as for the connection limit on MySQL you can change the threshold
on that in the config file. Also you might want to think about reusing
your connection PConnect. For me this works much better, but for some
they experience problems.

Mike

[Bernd Kohler]

Thanx!!
But what do you mean by caching the queries?
I'm already using many JOINs to keep the number of queries as low as
possible.
What do you think about the session-variables? Can they be handled quick
enough or will they slow down the server?

First off 3.x is not the MySQL installation you should be using. 4.0 or
4.1 you should be using now. Also your PHP installation is dated where
PHP is at version 4.3.11.

Now as for users, I have never had MySQL break down. I also have 50
websites that run off of one database with one administration panel. These
sites get fairly heavy traffic.

Now what you should pretty much do is Cache your queries so you are not
always querying the database. Only query when you need to. Make use of
JOIN statements, so you are not constantly querying for data that could be
in one query. Sometimes these JOINs can get a bit messy because some of
them are INNER JOINS some are LEFT OUTER JOINS etc.. And you end up with a
paragraph long query. But simply, it runs much faster.

Make sure you have index's on certain id's and things you will be looking
up regularly such as usernames and passwords, this allows you to do an
index query. These are normally very quick.

Also as for the connection limit on MySQL you can change the threshold on
that in the config file. Also you might want to think about reusing your
connection PConnect. For me this works much better, but for some they
experience problems.

Mike