Which is better

Dear all,

Just want to know which join is better for querying data faster.

I have 2 tables A ( 70 GB ) & B ( 7 MB )

A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.

select p.* from table A p, B q where p.id=q.id

or

select p.* from table B q , A p where q.id=p.id


Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Which is better

I hope the former better.
Test with query plan output, though

Best Rgs,
Shafi AHMED


-----Original Message-----
From: Adarsh Sharma [mailto:adarsh.sharma@orkash.com]
Sent: Tuesday, August 02, 2011 12:12 PM
To: mysql@lists.mysql.com
Subject: Which is better

Dear all,

Just want to know which join is better for querying data faster.

I have 2 tables A ( 70 GB ) & B ( 7 MB )

A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.

select p.* from table A p, B q where p.id=q.id

or

select p.* from table B q , A p where q.id=p.id


Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=shafi.ahmed@sifycorp.com



Get your world in your inbox!

Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id!
Log on to http://www.sify.com

********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to
Sify Technologies Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail & notify us
immediately at admin@sifycorp.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Too many aliases

>>>> 2011/08/02 12:11 +0530, Adarsh Sharma >>>>
select p.* from table A p, B q where p.id=q.id

or

select p.* from table B q , A p where q.id=p.id
<<<<<<<<
Why do people constantly change table names for queries, although, as here, it gain them nothing? It often makes for less clarity (for which table is this an alias???). This I would write

select A.* from A JOIN B USING (id)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Which is better

--20cf307ca40630b2ac04a9894080
Content-Type: text/plain; charset=ISO-8859-1

is optimizer depend on size of table? not sure

On Mon, Aug 1, 2011 at 11:48 PM, Shafi AHMED wrote:

> I hope the former better.
> Test with query plan output, though
>
> Best Rgs,
> Shafi AHMED
>
>
> -----Original Message-----
> From: Adarsh Sharma [mailto:adarsh.sharma@orkash.com]
> Sent: Tuesday, August 02, 2011 12:12 PM
> To: mysql@lists.mysql.com
> Subject: Which is better
>
> Dear all,
>
> Just want to know which join is better for querying data faster.
>
> I have 2 tables A ( 70 GB ) & B ( 7 MB )
>
> A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.
>
> select p.* from table A p, B q where p.id=q.id
>
> or
>
> select p.* from table B q , A p where q.id=p.id
>
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=shafi.ahmed@sifycorp.com
>
>
>
> Get your world in your inbox!
>
> Mail, widgets, documents, spreadsheets, organizer and much more with your
> Sifymail WIYI id!
> Log on to http://www.sify.com
>
> ********** DISCLAIMER **********
> Information contained and transmitted by this E-MAIL is proprietary to
> Sify Technologies Limited and is intended for use only by the individual or
> entity to
> which it is addressed, and may contain information that is privileged,
> confidential or exempt from disclosure under applicable law. If this is a
> forwarded message, the content of this E-MAIL may not have been sent with
> the authority of the Company. If you are not the intended recipient, an
> agent of the intended recipient or a person responsible for delivering the
> information to the named recipient, you are notified that any use,
> distribution, transmission, printing, copying or dissemination of this
> information in any way or in any manner is strictly prohibited. If you have
> received this communication in error, please delete this mail & notify us
> immediately at admin@sifycorp.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=aim.prabhat@gmail.com
>
>


--
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--20cf307ca40630b2ac04a9894080--

Re: Which is better

On 8/2/2011 02:41, Adarsh Sharma wrote:
> Dear all,
>
> Just want to know which join is better for querying data faster.
>
> I have 2 tables A ( 70 GB ) & B ( 7 MB )
>
> A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.
>
> select p.* from table A p, B q where p.id=q.id
>
> or
>
> select p.* from table B q , A p where q.id=p.id
>
>
> Thanks
>

There is no difference in performance. The optimizer will change the
sequence it uses to read the tables according to its own rules.

If you had used STRAIGHT JOIN to force a particular execution path, the
it would normally be faster to read the smaller table first.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Which is better

Hard to tell. It depends on the cardinality of tables' id (I assume the
IDs are not unique in each of the tables). David.

-----Original Message-----
From: Shawn Green (MySQL) [mailto:shawn.l.green@oracle.com]=20
Sent: Tuesday, August 02, 2011 2:47 PM
To: Adarsh Sharma
Cc: mysql@lists.mysql.com
Subject: Re: Which is better

On 8/2/2011 02:41, Adarsh Sharma wrote:
> Dear all,
>
> Just want to know which join is better for querying data faster.
>
> I have 2 tables A ( 70 GB ) & B ( 7 MB )
>
> A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.
>
> select p.* from table A p, B q where p.id=3Dq.id
>
> or
>
> select p.* from table B q , A p where q.id=3Dp.id
>
>
> Thanks
>

There is no difference in performance. The optimizer will change the=20
sequence it uses to read the tables according to its own rules.

If you had used STRAIGHT JOIN to force a particular execution path, the=20
it would normally be faster to read the smaller table first.

--=20
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Ddlerer@us.univision.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Too many aliases

> >>>> 2011/08/02 12:11 +0530, Adarsh Sharma >>>>
>
> select p.* from table A p, B q where p.id=q.id
>
> or
>
> select p.* from table B q , A p where q.id=p.id
> <<<<<<<<
> Why do people constantly change table names for queries, although, as here,
> it gain them nothing? It often makes for less clarity (for which table is
> this an alias???).

Depens on your table names. I rather like being able to give a short
description rather then long table names if someone decided that as a
tablename. I doubt your example with already short tablenames is one from real
life, but if you saw someone doing it would indeed be a waste of time. But the
main thing is it helps to distinguish tables in joins having the same table
more then once (and of course results from subqueries etc.):

SELECT first.*
FROM tablename first
LEFT JOIN tablename second
ON first.some_id = second.some_id
AND first.id != second.id
WHERE second.id IS NULL
--
Rik Wasmus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Too many aliases

I rarely use aliases (unless rarely required in self-join queries).
When I have that option, I create unique columns by prefixing every
table (and its objects) with a number.
Something like:
Create table T1234_Employee
(C1234_Employee_id number(5),
C1234_employee_status char(1)...)
Index X1234_Employee_Id on .... Etc.

Yes, the column names may be longer this way, but easy to refer to and
easy to communicate (by specifying a table number). I wonder what others
think about it.

David.

-----Original Message-----
From: Rik Wasmus [mailto:rik@grib.nl]=20
Sent: Wednesday, August 03, 2011 6:47 AM
To: mysql@lists.mysql.com
Subject: Re: Too many aliases

> >>>> 2011/08/02 12:11 +0530, Adarsh Sharma >>>>
>=20
> select p.* from table A p, B q where p.id=3Dq.id
>=20
> or
>=20
> select p.* from table B q , A p where q.id=3Dp.id
> <<<<<<<<
> Why do people constantly change table names for queries, although, as
here,
> it gain them nothing? It often makes for less clarity (for which table
is
> this an alias???).

Depens on your table names. I rather like being able to give a short=20
description rather then long table names if someone decided that as a=20
tablename. I doubt your example with already short tablenames is one
from real=20
life, but if you saw someone doing it would indeed be a waste of time.
But the=20
main thing is it helps to distinguish tables in joins having the same
table=20
more then once (and of course results from subqueries etc.):

SELECT first.*=20
FROM tablename first
LEFT JOIN tablename second
ON first.some_id =3D second.some_id
AND first.id !=3D second.id
WHERE second.id IS NULL
--=20
Rik Wasmus

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Ddlerer@us.univision.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Too many aliases

--001636ed678a69241204a99bb8e4
Content-Type: text/plain; charset=ISO-8859-1

On Aug 3, 2011 9:24 AM, "David Lerer" wrote:
>
> I rarely use aliases (unless rarely required in self-join queries).
> When I have that option, I create unique columns by prefixing every
> table (and its objects) with a number.
> Something like:
> Create table T1234_Employee
> (C1234_Employee_id number(5),
> C1234_employee_status char(1)...)
> Index X1234_Employee_Id on .... Etc.
>
> Yes, the column names may be longer this way, but easy to refer to and
> easy to communicate (by specifying a table number). I wonder what others
> think about it.
>

Looks COBOL-ish (IIRC what COBOL looks like) :)

I much prefer shorter names but can agree that, if this leads to obscurity,
use the namespace the engine provided you. I don't like typing if I don't
have to but I *hate* saying, 'what the hell was I thinking'.

I generally call index fields ix_*, foreign key columns *_fk, primary key
columns *_pk, constraints table_field. That's about it.

--001636ed678a69241204a99bb8e4--

Re: Too many aliases

>>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
But the
main thing is it helps to distinguish tables in joins having the same table
more then once (and of course results from subqueries etc.):

SELECT first.*
FROM tablename first
LEFT JOIN tablename second
ON first.some_id = second.some_id
AND first.id != second.id
WHERE second.id IS NULL
<<<<<<<<
Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not.

Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Too many aliases

I agree. I use the same column name in all tables where it has the same
function - but I consistently add a suffix or prefix. And yes, it is the
old fashion way.... David.

-----Original Message-----
From: hsv@tbbs.net [mailto:hsv@tbbs.net]=20
Sent: Thursday, August 04, 2011 8:26 AM
To: rik@grib.nl
Cc: mysql@lists.mysql.com
Subject: Re: Too many aliases

>>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
But the=20
main thing is it helps to distinguish tables in joins having the same
table=20
more then once (and of course results from subqueries etc.):

SELECT first.*=20
FROM tablename first
LEFT JOIN tablename second
ON first.some_id =3D second.some_id
AND first.id !=3D second.id
WHERE second.id IS NULL=20
<<<<<<<<
Well, yes, here it is needful. But it seems to me from most of the
examples that people here post, that they have the idea that it is the
style always to use one-letter aliases, whether it is helpful or not.

Now I do not do this, but I often see examples where a field for one
purpose has in one table one name, and in another table another,
slightly different, name, and then, too, I see alias used, although, in
this case, no table name at all is needed. (I like to use the same field
name in all tables where it has the same function.)


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Ddlerer@us.univision.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Too many aliases

>-----Original Message-----
>From: David Lerer [mailto:DLerer@us.univision.com]
>Sent: Wednesday, August 03, 2011 10:25 AM
>To: mysql@lists.mysql.com
>Subject: RE: Too many aliases
>
>I rarely use aliases (unless rarely required in self-join queries).
>Yes, the column names may be longer this way, but easy to refer to and
>easy to communicate (by specifying a table number). I wonder what others
>think about it.
>
[JS] Back when I was trying to fit as much code on a punch-card as possible,
the languages only supported short names, and the terminals ran at 10cps,
every keystroke was precious.

Now I routinely pay the price of extra keystrokes for readability: not just
with things such as column names, but with parentheses and the like as well.
Like you, I only use aliases when necessary.

If I want brain-teasers, I'll do a crossword puzzle.

But that's just me.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.giiresearch.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Too many aliases

Well, while we're on the subject of SQL style, can anyone tell me why I'm
always seeing people prefixing the name of a table with something like "tbl?"

For example:

create table tblCUSTOMERS ( ... );

Seems to me that you should probably know that CUSTOMERS is a table, or is it
just me?

Looking forward to your input.

Mike.

On Thursday 04 August 2011 6:43:55 am David Lerer wrote:
> I agree. I use the same column name in all tables where it has the same
> function - but I consistently add a suffix or prefix. And yes, it is the
> old fashion way.... David.
>
> -----Original Message-----
> From: hsv@tbbs.net [mailto:hsv@tbbs.net]
> Sent: Thursday, August 04, 2011 8:26 AM
> To: rik@grib.nl
> Cc: mysql@lists.mysql.com
> Subject: Re: Too many aliases
>
> >>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
>
> But the
> main thing is it helps to distinguish tables in joins having the same
> table
> more then once (and of course results from subqueries etc.):
>
> SELECT first.*
> FROM tablename first
> LEFT JOIN tablename second
> ON first.some_id = second.some_id
> AND first.id != second.id
> WHERE second.id IS NULL
> <<<<<<<<
> Well, yes, here it is needful. But it seems to me from most of the
> examples that people here post, that they have the idea that it is the
> style always to use one-letter aliases, whether it is helpful or not.
>
> Now I do not do this, but I often see examples where a field for one
> purpose has in one table one name, and in another table another,
> slightly different, name, and then, too, I see alias used, although, in
> this case, no table name at all is needed. (I like to use the same field
> name in all tables where it has the same function.)

--

Take care and have fun,
Mike Diehl.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Too many aliases

--00261887667226d15104a9af8839
Content-Type: text/plain; charset=ISO-8859-1

http://en.wikipedia.org/wiki/Hungarian_notation


On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl wrote:

> Well, while we're on the subject of SQL style, can anyone tell me why I'm
> always seeing people prefixing the name of a table with something like
> "tbl?"
>
> For example:
>
> create table tblCUSTOMERS ( ... );
>
> Seems to me that you should probably know that CUSTOMERS is a table, or is
> it
> just me?
>
> Looking forward to your input.
>
> Mike.
>
> On Thursday 04 August 2011 6:43:55 am David Lerer wrote:
> > I agree. I use the same column name in all tables where it has the same
> > function - but I consistently add a suffix or prefix. And yes, it is the
> > old fashion way.... David.
> >
> > -----Original Message-----
> > From: hsv@tbbs.net [mailto:hsv@tbbs.net]
> > Sent: Thursday, August 04, 2011 8:26 AM
> > To: rik@grib.nl
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Too many aliases
> >
> > >>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
> >
> > But the
> > main thing is it helps to distinguish tables in joins having the same
> > table
> > more then once (and of course results from subqueries etc.):
> >
> > SELECT first.*
> > FROM tablename first
> > LEFT JOIN tablename second
> > ON first.some_id = second.some_id
> > AND first.id != second.id
> > WHERE second.id IS NULL
> > <<<<<<<<
> > Well, yes, here it is needful. But it seems to me from most of the
> > examples that people here post, that they have the idea that it is the
> > style always to use one-letter aliases, whether it is helpful or not.
> >
> > Now I do not do this, but I often see examples where a field for one
> > purpose has in one table one name, and in another table another,
> > slightly different, name, and then, too, I see alias used, although, in
> > this case, no table name at all is needed. (I like to use the same field
> > name in all tables where it has the same function.)
>
> --
>
> Take care and have fun,
> Mike Diehl.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--00261887667226d15104a9af8839--

Re: Too many aliases

>>>> 2011/08/04 10:21 -0500, Johnny Withers >>>>
http://en.wikipedia.org/wiki/Hungarian_notation


On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl wrote
> Well, while we're on the subject of SQL style, can anyone tell me why I'm
> always seeing people prefixing the name of a table with something like
> "tbl?"

<<<<<<<<
Yeah, but why perpetuate such a thing in a language that has type (at least 'table' is distinct)? BCPL had at all no type.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Too many aliases

It's simple... ttwwadi is the only reason I assume.=20

Sent from my iPad

On Aug 5, 2011, at 2:39 PM, (Hal�sz S�ndor=
) hsv@tbbs.net wrote:

>>>>> 2011/08/04 10:21 -0500, Johnny Withers >>>>
> http://en.wikipedia.org/wiki/Hungarian_notation
>=20
>=20
> On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl wrote
>> Well, while we're on the subject of SQL style, can anyone tell me why I'm=

>> always seeing people prefixing the name of a table with something like
>> "tbl?"
>=20
> <<<<<<<<
> Yeah, but why perpetuate such a thing in a language that has type (at leas=
t 'table' is distinct)? BCPL had at all no type.
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohnny@pixelated.n=
et
>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg