|
|
Posted by Erwin Moller on 08/09/06 08:38
Fairul Izham wrote:
>
> "Erwin Moller"
> <since_humans_read_this_I_am_spammed_too_much@spamyourself.com> wrote in
> message news:44d8829f$0$4525$e4fe514c@news.xs4all.nl...
>> Fairul Izham wrote:
>>
>>>
>>> "Erwin Moller"
>>> <since_humans_read_this_I_am_spammed_too_much@spamyourself.com> wrote in
>>> message news:44d0b41a$0$4531$e4fe514c@news.xs4all.nl...
>>>> Fairul Izham wrote:
>>>>
>>>>> Hi experts,
>>>>>
>>>>> Need some opinion,
>>>>>
>>>>> in my script, I create 3 database connection looks like this
>>>>>
>>>>> $db[1] = mysql_connect($server2, $username2, $password2, TRUE) or
>>>>> die("Server ".$server2." not found");
>>>>> mysql_select_db($database2,$db[1]) or die("Database ".$database2." not
>>>>> found");
>>>>>
>>>>> $db[2] = mysql_connect($server3, $username3, $password3, TRUE) or
>>>>> die("Server ".$server3." not found");
>>>>> mysql_select_db($database3,$db[2]) or die("Database ".$database3." not
>>>>> found");
>>>>>
>>>>> $db[3] = mysql_connect($server4, $username4, $password4, TRUE) or
>>>>> die("Server ".$server4." not found");
>>>>> mysql_select_db($database4,$db[3]) or die("Database ".$database4." not
>>>>> found");
>>>>>
>>>>> it is nicely deployed on busy webserver?
>>>>
>>>> Hi,
>>>>
>>>> What is your question?
>>>>
>>>> If you are wondering if making 3 connections is a problem: No.
>>>> Be sure you mention which connection each time you use one, because PHP
>>>> will
>>>> use the last one if you ommit the connectionparameter. A nice source of
>>>> potential bugs. :-)
>>>>
>>>> Regards,
>>>> Erwin Moller
>>>
>>> humm...
>>>
>>> mysql_connect ( [string server [, string username [, string password [,
>>> bool new_link [, int client_flags]]]]] )
>>>
>>> is it the connection parameter you mean is "bool new_link" ?
>>
>> Hi,
>>
>> I was away a few days. :P
>>
>> No, that is not what I ment.
>> With the mysql_connect you MAKE a connection and store that reference in
>> some variable.
>> When EXECUTING some query you can say on which connection.
>> (Check the docs at www.php.net.)
>> That is where you should take care to use the right one. If you ommit it
>> PHP
>> will use the last one.
>>
>>
>>> what i'm wondering is, if webserver open to many connection to mysql
>>> database, it will degrade the mysql database performance. I also think
>>> is it possible the mysql server will return error "to many connection"
>>> to webserver. when i try the code on my pc it's seems okey, but what
>>> happen when I use it on busy server. is there any possible issue will
>>> come out?
>>
>> Yes, theoretically you could be the one that uses the last available
>> connection(s).
>>
>> I wouldn't worry about that too much.
>> There is little overhead involved in making connection, true, but do not
>> think it is a lot. (I heard estimates of 2K per connection for mysql, not
>> sure excactly, but nothing to make fuzz about.)
>>
>> In general the quality of your databasedesign and the quality of your
>> queries is of MUCH more importance than the fact you use 3 connections.
>>
>> My advise would be: Focus your energy on good databasedesign. Understand
>> foreign keys, normalisation, etc. It helps you and the quality of your
>> applications a lot more, really. :-)
>>
>> Hope that helps.
>>
>> Regards,
>> Erwin Moller
>>
>
Hi,
> Haha... thanks a lot..
> maybe I need to focus on fast query and redesign my database.
>
> Do you think normalisation will help to speed up the query? I think, it
> only help to reduce data redundancy which is able to get more spaces.
Yes and No.
Why don't I ever give a straight answer? ;-)
Well, the way I look at it: It is like the chesschampion JanHein Donner said
about chess: "First you must learn all the rules and stupid points, like a
rook is 5 point, a bishop is 3, etc. Then forget them rules immediately, if
you want to raise above avarage levels."
It works the same with databasedesign.
You should start with designing your database neatly:
- normalized databases, never storing data twice.
- Enforce relationships with Foreign Keys.
- Give every table a autonumber Primary Key.
- etc.
Once you mastered that and feel confortable with it, you will surely run in
situation you mentioned: Some queries get really slow.
In such cases you can speed things up by giving up a little normalization,
as you said.
But:
(I can of course only speak with limited authority on this, coming from my
own experience and what I read.)
This can in my experience always be solved by adding one or two references
to primary keys from other tables in the table(s) that need them (of course
with a FK constaint on them).
If placed right, you can avoid scanning through a few extra tables, and
speeding up you queries a lot.
But this denormalization should only be used if you KNOW it will help.
It is a common mistake to think that tablescans are always slow. If scanning
on PK is done, they are always indexed, and extremely fast, even if such a
table grows to half a million records.
It is a mistake (in my humble opinion) to start thinking for the database.
Every serious database makes a good executionplan before starting the query.
This involves, amongst others:
- having an estimate of how big the relevant tables are, so it can optimize
the scans by joining smart.
- figuring out which parts in the whereclause are 'constant' and which are
part of the joins with other tables.
eg: WHERE ((lastname='Jackson') AND (tblarticles.userid=tblauthor.userid))
The executionplan will first make sure the rows with the right 'lastname'
are retrieved, then do the joins.
- etc.
PS: If you use prepared statements, most databases will STORE the
executionplan, so it gains speed by not having to think up a fresh
executionplan every query.
Or to put it otherwise: Have some faith in the smartness of the database.
All major databases are smart to a certain degree: Postgresql, Oracle,
MySQL, M$ Server, etc.
>
> The more you normalise your database table, the longer n slower sql query
> will be created. Humm.. is that right?
Yes. :-)
But preferable only denormalize AFTER you know where the bottleneck is. :-)
(Damn, I sound like some highschool teacher, sorry for that.)
Regards,
Erwin Moller
Navigation:
[Reply to this message]
|