Are Nested Queries Bad?

[furtech]

Hello,

I'm new to MySQL programming but I was wondering if someone could tell me if Nested Queries was 'bad' and if possible avoid them?

I've written some code that uses up to 7 subqueries.

Thanks,

Matthew

 

[feherke]

Hi

I've written some code that uses up to 7 subqueries.

Seven ? And you still just ask if they are bad ? On what kind of animal is your MySQL server running ? On the 1GHz snail I usually use, that would be boring slow.

I always avoid them if possible.

But you forget to mention where you use the sub-[tt]select[/tt]. According to my experience in general, not explicitely MySQL :

[b]select[/b] ([b]select[/b] field [b]from[/b] table)   [gray]-- very slow[/gray]
[b]from[/b] ([b]select[/b] fields [b]from[/b] table)   [gray]-- acceptably slow[/gray]
[b]where[/b] field=([b]select[/b] field [b]from[/b] table)  [gray]-- extremly slow[/gray]

Feherke.

http://rootshell.be/~feherke/

 

[furtech]

Yeah thanks...

Its running on my laptop (1.5g) but the DB is only several 100K (I think)... I'm just playing around with it - that's why I ask.

Matthew

 

[furtech]

Sorry I forgot to mention I was using where field=(select field from table).

I've pasted 2 bits of code which do the same thing...

Are you able to tell me if one is better than the other?

Firstly,

SELECT field1, field2, field3
FROM table1
WHERE field3 IN (SELECT field3
FROM table2
WHERE field4 IN (SELECT field4
FROM table3
WHERE field5 LIKE 'MEL%'
AND field4 IN (SELECT field4
FROM table4
WHERE field6 IN (SELECT field6
FROM table5
WHERE field7 IN (SELECT field7
FROM table6
WHERE field8 = '123456789')))));

Secondly,
SELECT table1.field1, table1.field2, table1.field3
FROM table1, table2, table3, table4, table5, table6
WHERE table1.field3 = table2.field3 AND table2.field4 = table3.field4 AND table3.field5 LIKE 'ABC%' AND table3.field4 = table4.field4 AND table4.field6 = table5.field6 AND table5.field7 = table6.field7 and field8 = '123456789';

Or are the both as bad as each other? If so, can you give me some suggestions to point me in the right direction?

Thanks,

Matthew

 

[TonyGroves]

Your first suggestion is appalling; it would be extremely inefficient and difficult to debug.

The second suggestion is better, but still difficult to follow the join conditions.

My suggestion is to use JOIN ... ON syntax, where each condition is specified as early as possible.