|
|
Posted by Fairul Izham on 08/11/06 02:37
"Erwin Moller"
<since_humans_read_this_I_am_spammed_too_much@spamyourself.com> wrote in
message news:44d99f02$0$4531$e4fe514c@news.xs4all.nl...
> 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
>
Thanks a lot,
hahaa... eventough sound like some highschool teacher... it's a good advice
for me thanks again
Navigation:
[Reply to this message]
|