List:General Discussion« Previous MessageNext Message »
From:mathias fatene Date:April 25 2005 10:06pm
Subject:RE: Query question
View as plain text  
Hi,
If my englsih is so bad, i'll try to explain and stop this thread now.
I'm not teaching, i'm answering questions. If someone wants to read
docs, he (she) doesn't ask a question on the list. So if i answer, i
answer the question, just the question.

You want to know my level of knowledgne, 10 years, oracle, sybase,
sqlserver, db2. I can help for migration from or to...
I said don't use joins for the query given in the example or queries
using just the joining columns from the first table.  Normal forms is
bla bla here ...

See also about covering indexes. That can help.

This is the query given by Jeff :
>>> So, if record 100 in table1 links to 5 corresponding records in
table2, 
>>> I want to pull the latest record from table2 where table2.parentid =
100 and table2.user not like 'john'

The only clause is about table2.parentid = 100  or child.id = 100.
that's the same.
All the other clauses are on table2. This is the exampel given by Jeff.

If you want absolutely LEFT outer joins for that (without other columns
from table1), i say you good luck, this can (also) do the trick.

That's all.

-----------------
If you give me real examples, i can help you to give you to find the
right (if i can) query plan. Tuning is my first target when i think a
query. 
I never suggest nested loops, but relationnal algebra.
I'm not supposed speeking to students but DBAs, for specific question. 

Sorry if i run up against your sensitivity, but we are not speaking
about the same thing.

And please if you have to criticize or complete an answer, it's your
right. The list is for that. If you want to speak to me as your student,
this is enough. I never did it when i was teacher 11 years ago.


Best Regards
--------------------
Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped] 
Sent: lundi 25 avril 2005 23:02
To: mathias fatene
Cc: 'Jeff McKeon'; mysql@stripped; peter.brawley@stripped
Subject: RE: Query question


"mathias fatene" <mfatene@stripped> wrote on 04/25/2005 04:24:42 PM:

> Hi,
> Im sorry to disappoint you but this is an anti-performance solution. 
> Use joins rathers than subqueries, and don't use joins if you can (all

> data in the mother table).
> 
> Imagine that table2 has 30.000.000 records, and not good indexes. you 
> can wait for your answer a long time.
> 
> Best Regards
> --------------------
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 
<snip>

Mathias,

I do appreciate your energy and willingness to contribute to the list. I

am not affiliated with MySQL or any of its subsidiaries and I have no 
special privileges to police what happens on this list. I am a fellow 
contributor just as you. With that said, I feel that I must seriously 
question your level of experience and ability to form useful responses.

When you say "and don't use joins if you can (all data in the mother 
table)", It seems to me that you are proposing that in order to
eliminate 
JOINs in queries that all data should be flattened into one single
table. 
Not only is this incorrect advice but it undermines the many reasons for

using a relational database system (RDBMS) in the first place. I would 
love to compare the performance of a properly normalized and indexed 
relational data structure against a single "flat" table for all but the 
most trivial of data sets.  The nomalized data will not only take up
less 
room on the disk but it will perform extremely well (especially for
larger 
data sets). The single-table model you proposed will not scale to more 
than a few hundred thousand rows before the table's size becomes a 
bottleneck.

Some queries will take "a long time" to finish against 30 million row 
tables, even with good indexes on them. Your extreme counter example was
a 
non-starter. The original poster acknowledges that they are new (no 
offence intended) and I feel that your posts were hardly helpful at best

and most likely counter-productive. Please, take the time to read your 
ansers from the perspective of the person you are responding to. Try to 
keep in mind not only their language skills (as this is a multi-national

list) but their experience level and even sometimes their age (we have 
many students looking for help on here and some of them are still 
teenagers). Please be more accurate, thoughtful, and descriptive the
next 
time you post, OK?

With greatest humility,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Thread
Query questionJeff McKeon25 Apr
  • Re: Query questionSGreen25 Apr
  • RE: Query questionmathias fatene25 Apr
    • RE: Query questionSGreen25 Apr
      • RE: Query questionmathias fatene25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
      • Re: Query questionPeter Brawley25 Apr
      • RE: Query questionSGreen25 Apr
        • RE: Query questionmathias fatene26 Apr
          • Re: Query questionMartijn Tonies26 Apr
            • RE: Query questionmathias fatene26 Apr
              • Re: Query questionChris Ramsay26 Apr
                • Re: Query questionmfatene26 Apr
  • Re: Query questionPeter Brawley25 Apr
  • RE: Query questionSGreen25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr