If you have a primary key then the join should be fully qualified. The main reason for slow joins is poor qualification. Do you have to have a DISTINCT in your sql? If so then your join qualification is not complete. You should look to be able to linke each row with exactly one other on the other table. If you have a primary key (and use this as a foriegn key all will be well. Unfortunatly without a lot more information it is difficult to know what to suggest.

Suffice to say that with the data volumes you are suggesting a two table join should return witnin seconds not minutes so you are right to optimise. Try getting some SQL analysis software and run your sql throught it. Look for full table scans.

Look in your sql for clauses that will defeat the indexes. Do you use TOUPPER LIKE or any similar sql. Rememeber it is all well having indexes only if the database can use them.

I HTH
--

Zigster

In reply to Re:(Zigster) (OT) Improving a SQL JOIN statement by zigster
in thread (OT) Improving a SQL JOIN statement by Masem

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.