Complex JOIN vs multiple selects

Hi,

if I have 3 or more table all 'linked' by an ID.
Is it better to have one query or 3 or more SELECT?

For example

// -- EXAMPLE A
SELECT something FROM t1 LEFT JOIN t2 ON t2.id=t1.id LEFT JOIN t3 ON
t3.id=t1.id WHERE t1.id=xx

// -- EXAMPLE B
SELECT something FROM t1 WHERE t1.id=xx
...
SELECT something FROM t2 WHERE t2.id = $t1id
SELECT something FROM t3 WHERE t3.id = $t1id

From the DB point of view does it really matter?
Doesn't MySQL break EXAMPLE A into EXAMPLE B?

And if I was to put it all into a stored procedure, should I keep the
LEFT JOIN or should I break it down into multiple selects?

I guess what I am asking is, does MySQL really care how I present it as
it will be hitting all 3 tables the same way in the end.

Thanks

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
(http://www.journalhome.com/) | 'webmaster Directory'
(http://www.webhostshunter.com/)
'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=24324

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

On 17 Jan, 06:50, FFMG <FFMG.33b...@no-mx.httppoint.com> wrote:
> Hi,
>
> if I have 3 or more table all 'linked' by an ID.
> Is it better to have one query or 3 or more SELECT?
>
> For example
>
> // -- EXAMPLE A
> SELECT something FROM t1 LEFT JOIN t2 ON t2.id=t1.id LEFT JOIN t3 ON
> t3.id=t1.id WHERE t1.id=xx
>
> // -- EXAMPLE B
> SELECT something FROM t1 WHERE t1.id=xx
> ..
> SELECT something FROM t2 WHERE t2.id = $t1id
> SELECT something FROM t3 WHERE t3.id = $t1id
>
> From the DB point of view does it really matter?
> Doesn't MySQL break EXAMPLE A into EXAMPLE B?
>
> And if I was to put it all into a stored procedure, should I keep the
> LEFT JOIN or should I break it down into multiple selects?
>
> I guess what I am asking is, does MySQL really care how I present it as
> it will be hitting all 3 tables the same way in the end.
>
> Thanks
>
> FFMG
>
> --
>
> 'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
> (http://www.journalhome.com/) | 'webmaster Directory'
> (http://www.webhostshunter.com/)
> 'Recreation Vehicle insurance'
> (http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
> redirection service' (http://urlkick.com/)
> ------------------------------------------------------------------------
> FFMG's Profile:http://www.httppoint.com/member.php?userid=580
> View this thread:http://www.httppoint.com/showthread.php?t=24324
>
> Message Posted via the webmaster forumhttp://www.httppoint.com, (Ad revenue sharing).

A JOINed query allows the optimiser to work out the best way to get
all the information. It also saves on the connection, transmission and
setup time overhead for each query.

>> Is it better to have one query or 3 or more SELECT?


> A JOINed query allows the optimiser to work out the best way to get
> all the information. It also saves on the connection, transmission and
> setup time overhead for each query.

And on the other hand, the join could cause some very large blob field
to be transmitted for each row, whereas 3 queries would do this only
once. So I guess the best answer is a bit situation-dependent.

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>>> Is it better to have one query or 3 or more SELECT?
>
>
>> A JOINed query allows the optimiser to work out the best way to get
>> all the information. It also saves on the connection, transmission and
>> setup time overhead for each query.
>
> And on the other hand, the join could cause some very large blob field
> to be transmitted for each row, whereas 3 queries would do this only
> once. So I guess the best answer is a bit situation-dependent.
>
> Regards,

And why would that be, Willem?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>> And on the other hand, the join could cause some very large blob field
>> to be transmitted for each row, whereas 3 queries would do this only
>> once. So I guess the best answer is a bit situation-dependent.
>>
>> Regards,
>
> And why would that be, Willem?

If you have, for instance a book table and an author table, and a book
is written by more than one author, you could write:

SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
book.id=5;

This would return the entire content of the book for each author
(far-fetched example, I know). Whereas:

SELECT content, @author_id:=authorId FROM book WHERE id=5;
SELECT author.name FROM author WHERE id=@author_id;

Would pass the content only once. So less data would go over the line,
but effectively the exact amount of data is given, because the duplicate
fields are removed.

Or am I wrong in this?

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On 18 Jan, 12:34, Willem Bogaerts
<w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> >> And on the other hand, the join could cause some very large blob field
> >> to be transmitted for each row, whereas 3 queries would do this only
> >> once. So I guess the best answer is a bit situation-dependent.
>
> >> Regards,
>
> > And why would that be, Willem?
>
> If you have, for instance a book table and an author table, and a book
> is written by more than one author, you could write:
>
> SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
> book.id=5;
>
> This would return the entire content of the book for each author
> (far-fetched example, I know). Whereas:
>
> SELECT content, @author_id:=authorId FROM book WHERE id=5;
> SELECT author.name FROM author WHERE id=@author_id;
>
> Would pass the content only once. So less data would go over the line,
> but effectively the exact amount of data is given, because the duplicate
> fields are removed.
>
> Or am I wrong in this?
>
> Regards,
> --
> Willem Bogaerts
>
> Application smith
> Kratz B.V.http://www.kratz.nl/

If book.id = 5 applied to more than one book then,
SELECT content, @author_id:=authorId FROM book WHERE id=5;
whould give you the content for every book where the id=5

Can't see the difference myself.

Captain Paralytic wrote:
> On 18 Jan, 12:34, Willem Bogaerts
> <w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
>>>> And on the other hand, the join could cause some very large blob field
>>>> to be transmitted for each row, whereas 3 queries would do this only
>>>> once. So I guess the best answer is a bit situation-dependent.
>>>> Regards,
>>> And why would that be, Willem?
>> If you have, for instance a book table and an author table, and a book
>> is written by more than one author, you could write:
>>
>> SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
>> book.id=5;
>>
>> This would return the entire content of the book for each author
>> (far-fetched example, I know). Whereas:
>>
>> SELECT content, @author_id:=authorId FROM book WHERE id=5;
>> SELECT author.name FROM author WHERE id=@author_id;
>>
>> Would pass the content only once. So less data would go over the line,
>> but effectively the exact amount of data is given, because the duplicate
>> fields are removed.
>>
>> Or am I wrong in this?
>>
>> Regards,
>> --
>> Willem Bogaerts
>>
>> Application smith
>> Kratz B.V.http://www.kratz.nl/
>
> If book.id = 5 applied to more than one book then,
> SELECT content, @author_id:=authorId FROM book WHERE id=5;
> whould give you the content for every book where the id=5
>
> Can't see the difference myself.

SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
book.id=5;

This is valid syntax, however, if you are not also restricting your
query to include a single author, it shows a very poor working knowledge
of your data does it not?

onedbguru

Willem Bogaerts wrote:
>>> And on the other hand, the join could cause some very large blob field
>>> to be transmitted for each row, whereas 3 queries would do this only
>>> once. So I guess the best answer is a bit situation-dependent.
>>>
>>> Regards,
>> And why would that be, Willem?
>
> If you have, for instance a book table and an author table, and a book
> is written by more than one author, you could write:
>
> SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
> book.id=5;
>

Why would it? You asked for book.id = 5. It will return one row
(assuming book.id is a unique field).

> This would return the entire content of the book for each author
> (far-fetched example, I know). Whereas:
>
> SELECT content, @author_id:=authorId FROM book WHERE id=5;
> SELECT author.name FROM author WHERE id=@author_id;
>
> Would pass the content only once. So less data would go over the line,
> but effectively the exact amount of data is given, because the duplicate
> fields are removed.
>
> Or am I wrong in this?
>
> Regards,

You are incorrect. In fact, the second one will require two calls to
the database to retrieve the same information the first one did in one call.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Michael Austin wrote:
> Captain Paralytic wrote:
>> On 18 Jan, 12:34, Willem Bogaerts
>> <w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
>>>>> And on the other hand, the join could cause some very large blob field
>>>>> to be transmitted for each row, whereas 3 queries would do this only
>>>>> once. So I guess the best answer is a bit situation-dependent.
>>>>> Regards,
>>>> And why would that be, Willem?
>>> If you have, for instance a book table and an author table, and a book
>>> is written by more than one author, you could write:
>>>
>>> SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
>>> book.id=5;
>>>
>>> This would return the entire content of the book for each author
>>> (far-fetched example, I know). Whereas:
>>>
>>> SELECT content, @author_id:=authorId FROM book WHERE id=5;
>>> SELECT author.name FROM author WHERE id=@author_id;
>>>
>>> Would pass the content only once. So less data would go over the line,
>>> but effectively the exact amount of data is given, because the duplicate
>>> fields are removed.
>>>
>>> Or am I wrong in this?
>>>
>>> Regards,
>>> --
>>> Willem Bogaerts
>>>
>>> Application smith
>>> Kratz B.V.http://www.kratz.nl/
>>
>> If book.id = 5 applied to more than one book then,
>> SELECT content, @author_id:=authorId FROM book WHERE id=5;
>> whould give you the content for every book where the id=5
>>
>> Can't see the difference myself.
>
> SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
> book.id=5;
>
> This is valid syntax, however, if you are not also restricting your
> query to include a single author, it shows a very poor working knowledge
> of your data does it not?
>
> onedbguru
>

No, it doesn't. It just means the book.id is a unique key. It's very
common to do something like this - i.e. show a list of books on a topic
and let the user pick one. The unique id is used to retrieve the data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Michael Austin wrote:
> Captain Paralytic wrote:
>> On 18 Jan, 12:34, Willem Bogaerts
>> If book.id = 5 applied to more than one book then,
>> SELECT content, @author_id:=authorId FROM book WHERE id=5;
>> whould give you the content for every book where the id=5
>>
>> Can't see the difference myself.
>
> SELECT book.content, author.name FROM book NATURAL JOIN author WHERE
> book.id=5;
>
> This is valid syntax, however, if you are not also restricting your
> query to include a single author, it shows a very poor working
> knowledge of your data does it not?

What the h*ll are you talking about? What data of mine do I not know about?
I didn't start this thread. I never even posted a query on it???