Re: General Mysql Connect

On Wed, 2008-10-29 at 19:57 -0500, Micah Gersten wrote: > Ashley Sheridan wrote: > > On Wed, 2008-10-29 at 19:49 -0500, Micah Gersten wrote: > > > >> Ashley Sheridan wrote: > >> > >>> On Wed, 2008-10-29 at 19:43 -0500, Micah Gersten wrote: > >>> > >>> > >>>> Ashley Sheridan wrote: > >>>> > >>>> > >>>>> On Wed, 2008-10-29 at 19:25 -0500, Micah Gersten wrote: > >>>>> > >>>>> > >>>>> > >>>>>> Ashley Sheridan wrote: > >>>>>> > >>>>>> > >>>>>> > >>>>>>> On Thu, 2008-10-30 at 08:55 +1100, Chris wrote: > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>>> Waynn Lue wrote: > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>>> I sent an email to the mysql list, but it reminded me of a > >>>>>>>>> question I had > >>>>>>>>> for people structuring their PHP code. What's the > >>>>>>>>> general way that people > >>>>>>>>> structure their connections? Right now, I spawn off two > >>>>>>>>> mysql_connect calls > >>>>>>>>> at the top of the file that includes my database calls, > >>>>>>>>> using "true" for the > >>>>>>>>> fourth parameters, so as to create two new connections. > >>>>>>>>> Then I use those > >>>>>>>>> two connections for two different databases I have to > >>>>>>>>> query from. > >>>>>>>>> > >>>>>>>>> Is it better just to use mysql_select_db within the query > >>>>>>>>> function every > >>>>>>>>> time for the same connection? Should I use mysql_connect > >>>>>>>>> every time without > >>>>>>>>> using "true", so as to re-use connections. > >>>>>>>>> Should I be using pconnect > >>>>>>>>> instead? > >>>>>>>>> > >>>>>>>>> I spent some time looking for answers to these questions, > >>>>>>>>> but am getting > >>>>>>>>> conflicting answers. Some people think relying on the > >>>>>>>>> re-use of these > >>>>>>>>> functions is good, some think that explicit management is > >>>>>>>>> better. In > >>>>>>>>> general, how have people on the list found them? For > >>>>>>>>> example, is having > >>>>>>>>> constant mysql_select_db calls a problem? > >>>>>>>>> > >>>>>>>>> > >>>>>>>>> > >>>>>>>> Are they connecting as the same user and on the same server? > >>>>>>>> Then you > >>>>>>>> can replace with a mysql_select_db call. > >>>>>>>> > >>>>>>>> If they aren't both of those, you have no choice. > >>>>>>>> > >>>>>>>> No idea if it'll make much of a difference (performance > >>>>>>>> wise etc) but > >>>>>>>> I'd leave it as two connections. > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>> How difficult would it be to converge the 2 databases into one? > >>>>>>> This > >>>>>>> would obviously use less memory (not sure exactly how big the > >>>>>>> footprint > >>>>>>> of each connection is though) and will slightly speed up page > >>>>>>> display > >>>>>>> time (as you only have to wait for one connection to be made > >>>>>>> rather than > >>>>>>> two) > >>>>>>> > >>>>>>> > >>>>>>> Ash > >>>>>>> www.ashleysheridan.co.uk > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>> Generally you want separation of data. MySQL doesn't have a > >>>>>> problem > >>>>>> accessing another DB on the same server with the same connection. > >>>>>> Also, > >>>>>> how would database convergence use less memory? > >>>>>> > >>>>>> . > >>>>>> Thank you, > >>>>>> > >>>>>> > >>>>>> > >>>>> For arguments sake, open 1000 database connections, all to different > >>>>> databases. Now tell me that each connection doesn't have a footprint. > >>>>> At > >>>>> the end of the day, whist it may seem fine for a script to have 2 > >>>>> connections open, the least open the better. Imagine 100 users > >>>>> simultaneously accessing a page that opens 10 connections. Suddenly you > >>>>> have 200 connections open, not a great idea. If you could amalgamate the > >>>>> db's, you'd have half as many connections open. > >>>>> > >>>>> If you're still having trouble understanding why having two database > >>>>> connections open is bad (regardless of whether they are on the same > >>>>> server or not) the I think web development is the wrong career for you. > >>>>> > >>>>> > >>>>> Ash > >>>>> www.ashleysheridan.co.uk > >>>>> > >>>>> > >>>>> > >>>> The answer in your case is not to combine the DBs necessarily, but > >>>> consolidate the connections used. Like I said, you can use 2 MySQL DBs > >>>> on the same connection in PHP. There's no reason to sacrifice > >>>> separation of data. > >>>> > >>>> Thank you, > >>>> Micah Gersten > >>>> > >>>> > >>> I'm sure if you look at the OP codes on your suggestion, you'd still use > >>> the same memory as having two separate connections open, unless you > >>> closed one first. Thing is, opening and closing database connections has > >>> its own overheads. > >>> > >>> > >>> Ash > >>> www.ashleysheridan.co.uk > >>> > >>> > >> How is using one connection the same as having 2 open? You just change > >> databases if you want to, or use the fully qualified table name > >> (database.table) in your query. What extra overhead is there in that? > >> > >> Thank you, > >> Micah Gersten > > Having one connection open at a time has only the overhead of the > > opening and closing of connections. As far as I know, you can't have two > > databases open on one connection, but please correct me if I am wrong. > > > > > > Ash > > www.ashleysheridan.co.uk > > > With MySQL, you can change the DB from query to query with > mysql_select_db. The alternative as I stated in my last post is to use > the fully qualified table name (database.table) in your query. MySQL > doesn't care which DB you have open if you do that. In both of these > cases, the same connection is used. > > Thank you, > Micah Gersten > onShore Networks > Internal Developer > http://www.onshore.com > And you can say for certain that there's no extra overhead involved? Ash www.ashleysheridan.co.uk