Count unique data in column
This is a discussion on Count unique data in column within the MySQL forums, part of the Database Server Software category; --> Jerry Stuckle wrote: > kev wrote: > > Axel Schwenke wrote: > > > >>Bill Karwin <bill@karwin.com> wrote: > ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 07:47 AM
| ||||
| ||||
 Re: Count unique data in column Jerry Stuckle wrote: > kev wrote: > > Axel Schwenke wrote: > > > >>Bill Karwin <bill@karwin.com> wrote: > >> > >>>kev wrote: > >>> > >>>>Should I connect to the server on every query? > >>> > >>>No, I would not recommend this. Connecting costs a large amount of > >>>overhead. It has to find the host, establish a socket, authenticate > >>>your username and password, and set up other resources. This is > >>>actually pretty expensive to do, and you should avoid doing it for every > >>>query. > >> > >>I agree that one should not connect for every single query. > >>OTOH MySQL connects are pretty cheap, probably cheaper than the > >>underlying TCP connection setup in the network stack. > >> > >> > >>>>Should I use pconnect (OR connect and save connection handler) and > >>>>disconnect after the session has ended? > >>> > >>>I assume you mean mysql_pconnect(), part of the PHP mysql package. > >>>http://www.php.net/function.mysql-pconnect > >>> > >>>Yes, this is a good thing to do for greater scalability. > >> > >>No, it is not. Contrary, using mysql_pconnect() is considered harmful, > >>as it tends to hog the MySQL server with idle connections. A very > >>detailed (but German) discussion of the topic is here: > >> > >><http://kris.koehntopp.de/artikel/webtune/> > >> > >>In a nutshell: > >> > >>a) many connections: > >> > >>This problem arises if you use different connections (that is <host, > >>user, password> tupels) to connect to the database. If your scripts > >>(*all* scripts running in one Apache instance) use N different > >>connections, PHP will use up to N * max_clients connections to the > >>database - independent from the real webserver load. > >> > >>Contrary, if each of your scripts uses only M different connections > >>at a time (in most cases M=1) and refrains from using persistent > >>connections, you will have at most M * max_clients open connections > >>to the database. Plus, you will only reach that limit if your web- > >>server is fully loaded. > >> > >>Idle connections will eat memory and file descriptors. Not good. > >> > >> > >>b) MySQL sessions have state > >> > >>There are lots of objects in MySQL with session scope: locks, user > >>variables, temporary tables. Currently there is no way to reset a > >>session, so the best way to destroy those objects is to close the > >>connection. Additionally there is a hidden attribute - the selected > >>database (mysql_select_db(), USE <db>). If you re-use a MySQL > >>connection handle in PHP, you cannot know the selected database. > >>Therefore many database abstraction layers for PHP call > >>mysql_select_db() *mandatory* right before each mysql_query(). > >> > >>Forgotten locks may render your database useless. Forgotten temp. > >>tables will eat memory and/or disk space. User variables may spread > >>sensitive information between web applications. Very bad. > >> > >> > >>Conclusion: mysql_pconnect() solves a nonexistent problem. Also it > >>creates new problems. You should not use it. > >> > >> > >>XL > >>-- > >>Axel Schwenke, Senior Software Developer, MySQL AB > >> > >>Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/ > >>MySQL User Forums: http://forums.mysql.com/ > > > > > > Good to know. Thanks for the info. Now I have to figure out how to pass > > the SQL connection handle across pages/the session. > > > > Kev, > > You can't pass connections across pages - the connection will automatically > close at the end of the PHP script if you don't close it yourself (it's a good > idea to close it, though). > > But that's OK - you really don't want to keep the connection open while the user > goes to lunch. Just connect before your first MySQL and close the connection > after the last one (or at the end of the page). > > > -- > ================== > Remove the "x" from my email address > Jerry Stuckle > JDS Computer Training Corp. > jstucklex@attglobal.net > ================== I see. So its connection per page request.  Thanks again.Kevin      |
Linear Mode
