i think i need a refresher. if i have this:

SELECT SomeColumn
FROM SomeTable
JOIN
(
SELECT AnotherColumn
FROM AnotherTable
WHERE OneColumn = 'A Value'
) DerivedTable
ON DerivedTable.AnotherColumn = SomeColumn

then i'm writing a cartesian statement? meaning that for each record in
SomeTable causes DerivedTable to be run *every* time the join comparision is
made? i had thought that nested statements get executed first, and the
results cached as a temporary table. i had thought that the operation done
in the join at that point was in the same fashion as if both tables were
permanent. i hope that makes sense.

the other thing i've been told is that derived tables carry no indexing
which makes the join take longer to complete. is it possible to use indexes
on such derived tables, if this is true, so that i get better performance
when joining them to other resultsets?

what is correct? and, would you provide a resource that goes into the order
of these operations in detail?

tia,

me

Steve wrote:
>i think i need a refresher. if i have this:
>
> SELECT SomeColumn
> FROM SomeTable
> JOIN
> (
> SELECT AnotherColumn
> FROM AnotherTable
> WHERE OneColumn = 'A Value'
> ) DerivedTable
> ON DerivedTable.AnotherColumn = SomeColumn
>
> then i'm writing a cartesian statement? meaning that for each record
> in SomeTable causes DerivedTable to be run *every* time the join
> comparision is made? i had thought that nested statements get
> executed first, and the results cached as a temporary table. i had
> thought that the operation done in the join at that point was in the
> same fashion as if both tables were permanent. i hope that makes
> sense.
> the other thing i've been told is that derived tables carry no
> indexing which makes the join take longer to complete. is it possible
> to use indexes on such derived tables, if this is true, so that i get
> better performance when joining them to other resultsets?
>
> what is correct? and, would you provide a resource that goes into the
> order of these operations in detail?
>
> tia,
>
> me

Why are you not just joining?

"Paul Lautman" <paul.lautman@btinternet.com> wrote in message
news:5sqsgiF1albnoU1@mid.individual.net...
> Steve wrote:
>>i think i need a refresher. if i have this:
>>
>> SELECT SomeColumn
>> FROM SomeTable
>> JOIN
>> (
>> SELECT AnotherColumn
>> FROM AnotherTable
>> WHERE OneColumn = 'A Value'
>> ) DerivedTable
>> ON DerivedTable.AnotherColumn = SomeColumn
>>
>> then i'm writing a cartesian statement? meaning that for each record
>> in SomeTable causes DerivedTable to be run *every* time the join
>> comparision is made? i had thought that nested statements get
>> executed first, and the results cached as a temporary table. i had
>> thought that the operation done in the join at that point was in the
>> same fashion as if both tables were permanent. i hope that makes
>> sense.
>> the other thing i've been told is that derived tables carry no
>> indexing which makes the join take longer to complete. is it possible
>> to use indexes on such derived tables, if this is true, so that i get
>> better performance when joining them to other resultsets?
>>
>> what is correct? and, would you provide a resource that goes into the
>> order of these operations in detail?
>>
>> tia,
>>
>> me
>
> Why are you not just joining?

this is a sample. i'm troubleshooting a very lengthy query i wrote that is
now having performance issues - the number of rows is very large now.
anyway, this is an example of a situation.

i finally found the answer in the mysql manual after much digging. i was
correct. there are two general subquery types, correlated and uncorrelated.
the example i posted was uncorrelated as no part of the subquery references
a column of the outter query. in that case, "mysql executes uncorrelated
queries only once." so, i'm not loosing my mind - and the dba's at the web
hosting company can kiss my ass for telling me different...and for saying
that derived tables are unindexed. in, any, some, etc. specifically look for
indecies that can be used in look-up type statements.

i'd have almost wished i was wrong though, as then i'd be further along in
solving my actual problem.

"Steve" <no.one@example.com> wrote in message
news:aeX9j.1345$Jh1.416@newsfe02.lga...
>
> "Paul Lautman" <paul.lautman@btinternet.com> wrote in message
> news:5sqsgiF1albnoU1@mid.individual.net...
>> Steve wrote:
>>>i think i need a refresher. if i have this:
>>>
>>> SELECT SomeColumn
>>> FROM SomeTable
>>> JOIN
>>> (
>>> SELECT AnotherColumn
>>> FROM AnotherTable
>>> WHERE OneColumn = 'A Value'
>>> ) DerivedTable
>>> ON DerivedTable.AnotherColumn = SomeColumn
>>>
>>> then i'm writing a cartesian statement? meaning that for each record
>>> in SomeTable causes DerivedTable to be run *every* time the join
>>> comparision is made? i had thought that nested statements get
>>> executed first, and the results cached as a temporary table. i had
>>> thought that the operation done in the join at that point was in the
>>> same fashion as if both tables were permanent. i hope that makes
>>> sense.
>>> the other thing i've been told is that derived tables carry no
>>> indexing which makes the join take longer to complete. is it possible
>>> to use indexes on such derived tables, if this is true, so that i get
>>> better performance when joining them to other resultsets?
>>>
>>> what is correct? and, would you provide a resource that goes into the
>>> order of these operations in detail?
>>>
>>> tia,
>>>
>>> me
>>
>> Why are you not just joining?
>
> this is a sample. i'm troubleshooting a very lengthy query i wrote that is
> now having performance issues - the number of rows is very large now.
> anyway, this is an example of a situation.
>
> i finally found the answer in the mysql manual after much digging. i was
> correct. there are two general subquery types, correlated and
> uncorrelated. the example i posted was uncorrelated as no part of the
> subquery references a column of the outter query. in that case, "mysql
> executes uncorrelated queries only once." so, i'm not loosing my mind -
> and the dba's at the web hosting company can kiss my ass for telling me
> different...and for saying that derived tables are unindexed. in, any,
> some, etc. specifically look for indecies that can be used in look-up type
> statements.
>
> i'd have almost wished i was wrong though, as then i'd be further along in
> solving my actual problem.

problem solved. i had them 'up' the amount of cache alloted for keys and
such...and, here's the kicker, had them *enable* query caching. dumbasses!
hard to cache a derived table's results when the results are big...just
using RAM!!! where just one of 8 complex queries would take over 24 hours to
run, the whole ball of wax runs in just under 15 minutes!