Object : How to optimize mysql architecture for better web connection
?
Hello, I use phpMyAdmin 2.3.2 and MySQL 3.23.58 and I do not know how
to well-organize mysql architecture for the user convience and
speedness.
On my web site, I call all the stucture from one table (5 colums and
50 rows).
Today, 600.000 connections per day are made to this table.
Each time, the queries using PHP do selections (example> SELECT * FROM
table_website WHERE CODE like 'RLINK%'.
The problems comes more and more often : like
1. mysql_connect(): Host 'localhost.localdomain' is blocked because
of many connection errors.
2. or like the query is very slow.
and I have to reboot the linux server each week to try to avoid these
problems.
We would like to have more people coming and to increase the numbers
to 1 or 1.2M connections per day.
My questions would be :
1. On a architecture point of vue, is it better to have only One table
or to have multi-tables.
2. I read http://www.mysql.com/documentation/m...imisation.html
but I could not find advises concerning many connections to one single
table.
I did not changed any general variable provided in standard width
mysql.
From what you think, What kind of possible implementation or change in
architecture I could make ?
Please Help ! Thank you.
Solange
12 rue de Seine
Design and IT Salle d'exposition
Chicago and Paris
http://www.jssgallery.org and http://www.friend.fr
Here, is the tipical query I make to select items within the table :
<?php
srand ((double) microtime() * 948625);
include "/config/config.php";
$idCnx = mysql_connect($host ,$user,$pass ) or die("Impossible de se
connecter à la base de données");
mysql_select_db($bdd) or die("Impossible de se connecter à la base de
données");
$sql = "SELECT SUM(Proportion) as sum FROM `ISTable` ";
$result = @mysql_query($sql,$idCnx);
$r = @mysql_fetch_array($result);
$SumProportion=$r[sum];
$RANDNUM = rand (1,$SumProportion);
$sql = "SELECT ScriptLink,Proportion FROM `ISTable` ORDER BY
`Proportion` DESC ";
$result = @mysql_query($sql,$idCnx);
$SumProportion=0;
while(($r = @mysql_fetch_array($result)) AND
($SumProportion<$RANDNUM) )
{
$SumProportion=$SumProportion+$r[Proportion];
$HTMLContent=$r[ScriptLink];
}
echo $HTMLContent;
?>
