List:General Discussion« Previous MessageNext Message »
From:David Griffiths Date:September 27 2003 1:11am
Subject:Can someone explain the difference between these two queries?
View as plain text  
SELECT count(*)
FROM commercial_entity, country, user_account, address_list
LEFT JOIN state_province ON address_list.state_province_id =
state_province.state_province_id
LEFT JOIN contact_info ON address_list.contact_info_id =
contact_info.contact_info_id
WHERE address_list.address_type_id = 101
AND commercial_entity.commercial_entity_id =
address_list.commercial_entity_id
AND address_list.country_id = country.country_id
AND commercial_entity.user_account_id = user_account.user_account_id
AND user_account.user_role_id IN (101, 101);


SELECT count(*)
FROM commercial_entity, address_list, country, user_account
LEFT JOIN state_province ON address_list.state_province_id =
state_province.state_province_id
LEFT JOIN contact_info ON address_list.contact_info_id =
contact_info.contact_info_id
WHERE address_list.address_type_id = 101
AND commercial_entity.commercial_entity_id =
address_list.commercial_entity_id
AND address_list.country_id = country.country_id
AND commercial_entity.user_account_id = user_account.user_account_id
AND user_account.user_role_id IN (101, 101);

The first one takes 1.73 seconds, the other takes 0.34 seconds.

The only difference is the order of the tables (address_list comes last in
the FROM clause in the first example, and is in the middle of the FROM
clause in the second example)...

I stumbed on this when benchmarking Postgres vs MySQL, and accidentally
pulled the Postgres version of the query and put it into the MySQL window
(Postgres won't run the MySQL version, but MySQL runs the Postgres version).

Oracle has the concept of a "driving table". Is this a similar example?

David.


----- Original Message -----
From: "Mike R. Duncan" <miked@stripped>
To: "Director General: NEFACOMP" <dg@stripped>;
<myodbc@stripped>; <mysql@stripped>
Sent: Thursday, September 25, 2003 2:55 PM
Subject: RE: Ideas on creating connections


try adding a '\' before the comma like a \n in c++

-----Original Message-----
From: Director General: NEFACOMP [mailto:dg@stripped]
Sent: Thursday, September 25, 2003 10:41 AM
To: Roger Davis; myodbc@stripped; mysql@stripped
Subject: Re: Ideas on creating connections


Thank you for your time.
I don't want to be too much demanding.

I currently have a problem with MyODBC 3.51.
When my client machine is set to use French numbers (with a COMMA instead of
a PERIOD or DOT for floats), MyODBC does not let the decimal part to pass.
Venu told me he will try to look into this before the next release. It seems
I am impatient!!!

You said you are trying to move from DAO to using the MySQL DLL
(libmysql.dll).
Can we share some code using VB and the DLL?

Just send me a piece of code doing the following:
=====
- Creating a connection to the MySQL server,
- Running a DELETE query on the server (or any other query that doesn't need
to return values)
- Running a SELECT query against the MySQL server and
- Populating data of the RecordSet creating into a Flex grid
- Closing and Distroying the RecordSet
- Closing and Distroying the Connection
=====
One time I saw such a code on http://www.vbmysql.com but it seems to have
dissappeared.


Thanks
Emery
----- Original Message -----
From: "Roger Davis" <rogerd7215@stripped>
To: <myodbc@stripped>; <mysql@stripped>
Sent: Thursday, September 25, 2003 19:08
Subject: RE: Ideas on creating connections


> DAO is, I guess you would say, the predessor to ADO.  I am currently in
the
> process of getting rid of all the DAO code in the application and moving
to
> a custom recordset using the libmysql.dll.  Some of this is done in VB and
> it is still extremely fast for a VB based recordset.  Although there are
> numerous ways to do this, it is really difficult to tell what would be the
> best in your situation.  As has been mentioned before, if you are doing
> queries one after another in your application, then by all means connect
> once and pass all the queries through that connection.  If you a making
very
> few queries and you have limited resources on the server, then it would be
> best to connect only when you need to and drop the connection when you are
> done.  These are of course only my opinions based on what I have
experienced
> in the past.  Your experience may vary. Void where prohibited.
>
> Roger
>
> > -----Original Message-----
> > From: Director General: NEFACOMP [mailto:dg@stripped]
> > Sent: Thursday, September 25, 2003 12:48 PM
> > To: Roger Davis; myodbc@stripped; mysql@stripped
> > Subject: Re: Ideas on creating connections
> >
> >
> > I never use DAO.
> >
> > Is there any benefit over ADO?
> >
> >
> > Thanks
> > Emery
> > ----- Original Message -----
> > From: "Roger Davis" <rogerd7215@stripped>
> > To: "Director General: NEFACOMP" <dg@stripped>;
> > <myodbc@stripped>; <mysql@stripped>
> > Sent: Thursday, September 25, 2003 18:14
> > Subject: RE: Ideas on creating connections
> >
> >
> > > My experience is this.  I use DAO through MyODBC, and I also use the
> > > libmysql.dll with a custom recordset.  Connection times through both
> > options
> > > are extremely fast. Fast enough in my situation that it can
> > basically can
> > be
> > > ignored.  There are times when I open several connections to
> > the server at
> > > once and have yet to run into any problems.  If you strictly want to
use
> > ADO
> > > though, I would suggest that you open one connection to the server and
> > > process your queries through that connection.  Leave it open
> > for the life
> > of
> > > the program.  While the program is open, the connection to the
> > server will
> > > stay open and the thread for MySQL will be a "sleep".
> > >
> > > Just my $0.02.
> > >
> > > Roger
> > > > -----Original Message-----
> > > > From: Director General: NEFACOMP [mailto:dg@stripped]
> > > > Sent: Thursday, September 25, 2003 11:57 AM
> > > > To: myodbc@stripped; mysql@stripped
> > > > Subject: Ideas on creating connections
> > > >
> > > >
> > > > Hi groups,
> > > >
> > > > I need some experienced users to guide me on this issue.
> > > > I am developping an Application that will be accessing a MySQL
> > > > database through MyODBC.
> > > > The application is developped using Visual Basic.
> > > >
> > > > Since my application will be used simultaneously by more than 200
> > > > users, I want to know the implication of using one of the
> > > > following methods for connecting to the server:
> > > >
> > > > 1. At logon, I create a connection to the server and maintain it
> > > > throught the application life. By the application life I mean
> > > > that the connection stays open as long as the application is
> > > > loaded in the memory. So, for a user who is online for 3 hours,
> > > > the connection is on for those three hours, even if the user goes
> > > > out for a coffee.
> > > >
> > > > 2. I create a connection only when a query is about to be sent to
> > > > the server. That means, I create a connection when I want to
> > > > create an ADO recordset and run a query against the server. After
> > > > running my query, I distroy the connection.
> > > >
> > > >
> > > > Currently I am using the first option since I don't exactly what
> > > > it is required (time and resources) to make a connection to the
> > > > server. Will you please tell me how fast is to connect to the
> > > > server. If you advise me that this method is the best, I will add
> > > > functions to reconnect a dead connection.
> > > >
> > > >
> > > > Any ideas and advices are highly welcomed
> > > >
> > > >
> > > > Thanks,
> > > > __________________________________
> > > > NZEYIMANA Emery Fabrice
> > > > NEFA Computing Services, Inc.
> > > > P.O. Box 5078 Kigali
> > > > Office Phone: +250-51 11 06
> > > > Office Fax: +250-50 15 19
> > > > Mobile: +250-08517768
> > > > Email: dg@stripped
> > > > http://www.nefacomp.net/
> > > >
> > > > ---
> > > > Incoming mail is certified Virus Free.
> > > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > > Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003
> > > >
> > > ---
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003
> > >
> > >
> > > --
> > > MySQL ODBC Mailing List
> > > For list archives: http://lists.mysql.com/myodbc
> > > To unsubscribe:    http://lists.mysql.com/myodbc?unsub=1
> > >
> > >
> > >
> >
> >
> >
> > --
> > MySQL ODBC Mailing List
> > For list archives: http://lists.mysql.com/myodbc
> > To unsubscribe:
> http://lists.mysql.com/myodbc?unsub=1
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:    http://lists.mysql.com/myodbc?unsub=1
>
>
>



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=1


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
RE: Ideas on creating connectionsDan Greene25 Sep
RE: Ideas on creating connectionsMike R. Duncan26 Sep