RE: [PHP] Re: Is select_db necessary?
[snip] So perhaps it depends on your view point and preconceptions and we're both coming at the flexible and offloading arguments with different starting views. Anyway, I only asked out of curiosity which I think has been satisfied (i.e. ultimately I don't fully agree with you! :p). [/snip] No worries! We are discussing semantics and situations which are always unique. Our primary application is one that uses data from many disparate and discrete data sources, integrating these things to make life simpler for the end user. The methodology that I describe is quite useful in that situation. We do have other applications that use a single database and where we explicitly state the database using select_db; the database name never has to appear in the query at all. I occasionally hear a little whining and moaning about this as the queries become less self-documenting, forcing everyone to add more commentary to the code to make up for the deficiency. Actually I have found that during code review in these 'single database' applications many of the queries do have the database name stated. The devs have seen the value in this method across the board. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Is select_db necessary?
Wed, Aug 12, 2009 at 10:37 AM, Ralph Deffke ralph_def...@yahoo.de wrote: I agree totally, are we not dicussing speed issues all the time? and then we recommend a code doing an unessesary job on every call? an ANSI selct db in the sql forces any database to run the internal select db because there would be no check if the databse is the current one. because, databasedevelopers can espext some smartness of us, the programmers. its a lot off stuff to do for the database to select a database. for shure, the database leafs that IN OUR hand to avoid to force time consuming server resources. ralph ralph_def...@yahoo.de Colin Guthrie gm...@colin.guthr.ie wrote in message news:h5ug1h$tj...@ger.gmane.org... 'Twas brillig, and Jay Blanchard at 12/08/09 13:53 did gyre and gimble: Jay Blanchard wrote: SELECT a.foo, a.bar FROM myDatabase.myTable a WHERE you set other conditions here All that is required is that you establish a connection to a server. If I recall correctly, this will cause issues with replication in MySQL... insofar as you perform amodifying query. [/snip] You're correct with regards to queries that modify on replicated systems. If all you're doing is gathering data then this will work just fine, is somewhat self-documenting (especially in lengthier code containers), and very flexible. It also leaves the selection in the database's hands, and as we almost always say, let the database do the work when it can. I'm interested to know why you consider this to be very flexible and how this leaves the selection in the database's hands? If I were to implement this and they try some destructive testing/demo on a sacrificial database, I'd have to use a whole other server instance (as all the queries would hardcode in the db name). Is it not more flexible if you omit the table name in every single query and specify it once in your bootstrap/connection code? Thus doing tests on other dbs etc. is a pretty simple switch of the connection code. Also telling the db engine what database you want to use in every query is not, IMO, leaving the selection in the the database's hands. Just curious as to the rationale here :) Col -- Colin Guthrie gmane(at)colin.guthr.ie http://colin.guthr.ie/ Day Job: Tribalogic Limited [http://www.tribalogic.net/] Open Source: Mandriva Linux Contributor [http://www.mandriva.com/] PulseAudio Hacker [http://www.pulseaudio.org/] Trac Hacker [http://trac.edgewall.org/] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ?php $link = mysql_connect( /* settings */); mysql_select_db( 'database', $link); $result = mysql_query( 'SELECT * FROM table', $link ); What SQL was sent to the database? Looking at bin logs I've found this. 1. use database = mysql_select_db 2. use database: SELECT * FROM table = mysql_query The DB is usually a common bottle-neck for most applications. You can have several webservers, but can't do that with the DB... of course, you can have multiples slaves but just 1 master. is this the best way to send queries? What's the better and faster way? -- Martin Scotta
Re: [PHP] Re: Is select_db necessary?
as i said earlier: on db level there is always al select db done, doing this on higer level layers (the database extension) consumes time. or why do extension have the two ways of functions? to make our live more difficult? on a ANSI sql the sql interpreter time is increased! unnessarylie ralph_def...@yahoo.de Martin Scotta martinsco...@gmail.com wrote in message news:6445d94e0908120718g6c5bf368tacf8bbad127b5...@mail.gmail.com... Wed, Aug 12, 2009 at 10:37 AM, Ralph Deffke ralph_def...@yahoo.de wrote: I agree totally, are we not dicussing speed issues all the time? and then we recommend a code doing an unessesary job on every call? an ANSI selct db in the sql forces any database to run the internal select db because there would be no check if the databse is the current one. because, databasedevelopers can espext some smartness of us, the programmers. its a lot off stuff to do for the database to select a database. for shure, the database leafs that IN OUR hand to avoid to force time consuming server resources. ralph ralph_def...@yahoo.de Colin Guthrie gm...@colin.guthr.ie wrote in message news:h5ug1h$tj...@ger.gmane.org... 'Twas brillig, and Jay Blanchard at 12/08/09 13:53 did gyre and gimble: Jay Blanchard wrote: SELECT a.foo, a.bar FROM myDatabase.myTable a WHERE you set other conditions here All that is required is that you establish a connection to a server. If I recall correctly, this will cause issues with replication in MySQL... insofar as you perform amodifying query. [/snip] You're correct with regards to queries that modify on replicated systems. If all you're doing is gathering data then this will work just fine, is somewhat self-documenting (especially in lengthier code containers), and very flexible. It also leaves the selection in the database's hands, and as we almost always say, let the database do the work when it can. I'm interested to know why you consider this to be very flexible and how this leaves the selection in the database's hands? If I were to implement this and they try some destructive testing/demo on a sacrificial database, I'd have to use a whole other server instance (as all the queries would hardcode in the db name). Is it not more flexible if you omit the table name in every single query and specify it once in your bootstrap/connection code? Thus doing tests on other dbs etc. is a pretty simple switch of the connection code. Also telling the db engine what database you want to use in every query is not, IMO, leaving the selection in the the database's hands. Just curious as to the rationale here :) Col -- Colin Guthrie gmane(at)colin.guthr.ie http://colin.guthr.ie/ Day Job: Tribalogic Limited [http://www.tribalogic.net/] Open Source: Mandriva Linux Contributor [http://www.mandriva.com/] PulseAudio Hacker [http://www.pulseaudio.org/] Trac Hacker [http://trac.edgewall.org/] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ?php $link = mysql_connect( /* settings */); mysql_select_db( 'database', $link); $result = mysql_query( 'SELECT * FROM table', $link ); What SQL was sent to the database? Looking at bin logs I've found this. 1. use database = mysql_select_db 2. use database: SELECT * FROM table = mysql_query The DB is usually a common bottle-neck for most applications. You can have several webservers, but can't do that with the DB... of course, you can have multiples slaves but just 1 master. is this the best way to send queries? What's the better and faster way? -- Martin Scotta -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Is select_db necessary?
On Wed, Aug 12, 2009 at 11:41 AM, Ralph Deffke ralph_def...@yahoo.dewrote:
what are telling the logs on that code?
?php
$link = mysql_connect( /* settings */);
mysql_select_db( 'database', $link);
$result = mysql_query( 'SELECT * FROM table', $link );
$result = mysql_query( 'SELECT * FROM anothertable', $link );
$result = mysql_query( 'SELECT * FROM anothertable', $link );
$result = mysql_query( 'SELECT * FROM anothertable', $link );
$result = mysql_query( 'SELECT * FROM anothertable', $link );
$result = mysql_query( 'SELECT * FROM table', $link );
would be interesting to see.
I personaly woudn't spend the time on logs, a computer is logical, I try to
be logical, and I would
try to create code which is logical speedy. I expect the database kernel
programmer the same.
I think then we are on the secure side.
ralph_def...@yahoo.de
--
*Von:* Martin Scotta martinsco...@gmail.com
*An:* Ralph Deffke ralph_def...@yahoo.de
*CC:* php-general@lists.php.net
*Gesendet:* Mittwoch, den 12. August 2009, 16:18:01 Uhr
*Betreff:* Re: [PHP] Re: Is select_db necessary?
Wed, Aug 12, 2009 at 10:37 AM, Ralph Deffke ralph_def...@yahoo.de wrote:
I agree totally, are we not dicussing speed issues all the time? and then
we
recommend a code doing an unessesary job on every call?
an ANSI selct db in the sql forces any database to run the internal select
db because there would be no check if the databse is the current one.
because, databasedevelopers can espext some smartness of us, the
programmers. its a lot off stuff to do for the database to select a
database. for shure, the database leafs that IN OUR hand to avoid to force
time consuming server resources.
ralph
ralph_def...@yahoo.de
Colin Guthrie gm...@colin.guthr.ie wrote in message
news:h5ug1h$tj...@ger.gmane.org...
'Twas brillig, and Jay Blanchard at 12/08/09 13:53 did gyre and gimble:
Jay Blanchard wrote:
SELECT a.foo, a.bar
FROM myDatabase.myTable a
WHERE you set other conditions here
All that is required is that you establish a connection to a server.
If I recall correctly, this will cause issues with replication in
MySQL... insofar as you perform amodifying query.
[/snip]
You're correct with regards to queries that modify on replicated
systems. If all you're doing is gathering data then this will work
just
fine, is somewhat self-documenting (especially in lengthier code
containers), and very flexible. It also leaves the selection in the
database's hands, and as we almost always say, let the database do
the
work when it can.
I'm interested to know why you consider this to be very flexible and how
this leaves the selection in the database's hands?
If I were to implement this and they try some destructive testing/demo
on a sacrificial database, I'd have to use a whole other server instance
(as all the queries would hardcode in the db name).
Is it not more flexible if you omit the table name in every single query
and specify it once in your bootstrap/connection code? Thus doing tests
on other dbs etc. is a pretty simple switch of the connection code.
Also telling the db engine what database you want to use in every query
is not, IMO, leaving the selection in the the database's hands.
Just curious as to the rationale here :)
Col
--
Colin Guthrie
gmane(at)colin.guthr.ie
http://colin.guthr.ie/
Day Job:
Tribalogic Limited [http://www.tribalogic.net/]
Open Source:
Mandriva Linux Contributor [http://www.mandriva.com/]
PulseAudio Hacker [http://www.pulseaudio.org/]
Trac Hacker [http://trac.edgewall.org/]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
?php
$link = mysql_connect( /* settings */);
mysql_select_db( 'database', $link);
$result = mysql_query( 'SELECT * FROM table', $link );
What SQL was sent to the database?
Looking at bin logs I've found this.
1. use database = mysql_select_db
2. use database: SELECT * FROM table = mysql_query
The DB is usually a common bottle-neck for most applications.
You can have several webservers, but can't do that with the DB... of
course, you can have multiples slaves but just 1 master.
is this the best way to send queries?
What's the better and faster way?
--
Martin Scotta
Mysql only stores the SQL in the log when the query affect something in the
DB.
So, SELECTS do not appear in the binlog.
That said, this is the script I have runned
$link = mysql_connect( /*settings*/ );
mysql_select_db('devtool', $link);
mysql_query( 'TRUNCATE TABLE dev_activities', $link );
mysql_query( 'TRUNCATE TABLE dev_files', $link );
mysql_query( 'INSERT INTO dev_activities VALUES (1, 1)', $link );
mysql_query( 'INSERT INTO dev_files VALUES (1, 1)', $link );
mysql_query( 'INSERT INTO dev_activities VALUES (2, 2)', $link );
mysql_query( 'INSERT INTO dev_files VALUES (2, 2
RE: [PHP] Re: Is select_db necessary?
[snip]
I'm interested to know why you consider this to be very flexible and how
this leaves the selection in the database's hands?
[/snip]
Flexible because I can connect to more than one database on a server
using one connection without having to re-issue a select_db command,
especially in a code container requiring connection to multiple
databases.
It leaves the connection on the database side because the selection of
the database is performed in the query rather than in a separate query.
A select_db essentially issues a use database query which can be
avoided by including database selection in the query itself.
[snip]
If I were to implement this and they try some destructive testing/demo
on a sacrificial database, I'd have to use a whole other server instance
(as all the queries would hardcode in the db name).
[/snip]
I am unsure of what you're after here. We are only using a hard-coded
example but we can certainly improve this by using a class or function.
[snip]
Is it not more flexible if you omit the table name in every single query
and specify it once in your bootstrap/connection code? Thus doing tests
on other dbs etc. is a pretty simple switch of the connection code.
[/snip]
Sure it is, unless you have to connect to more than one database in any
given code container. Consider this, I include a database server
connection (one file) and I do not have to do a select_db in other
subsequent files if I include the database name in the SQL query itself;
include(inc/dataConnect.inc); // containing server connection only
Now in foo.php would you rather;
$theDatabaseSelected = select_db('database', $dbc);
$theQuery = SELECT foo FROM bar WHERE glorp;
Or;
$theQuery = SELECT a.foo FROM database.bar a WHERE glorp;
Now consider that I have to get information from more than one database
(on the same server) in a single container for display. Do you want to
issue the select_db each time?
$theDatabaseSelected = select_db('database', $dbc);
$theQuery = SELECT foo FROM bar WHERE glorp;
$theNextDatabaseSelected = select_db('nextDatabase', $dbc);
$theQuery = SELECT glorp FROM foo WHERE bar;
Or would it be easier to do this?
$theQuery = SELECT a.foo FROM database.bar a WHERE glorp;
$theNextQuery = SELECT a.glorp FROM database.foo a WHERE bar;
[snip]
Also telling the db engine what database you want to use in every query
is not, IMO, leaving the selection in the the database's hands.
[/snip]
Sure it is, if not you have to use PHP (select_db) to perform the
database selection which sends an additional query ('use database') to
the database system.
In other words, would you query all of the raw data out of the database
and use PHP to process that data when the database can do a much more
effective job of filtering out what you do not need?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
