SQL JOINs should scale nicely provided that the field(s) on which you are JOINing are indexed in all tables that are part of the join.

It's important to index all tables, rather than just the ones "on right right" because smart query optimizers will try to guess which of the two (or N) tables to scan first based in part on the number of rows in the table. If they happen to decide on the n>1st table, the 1st table had better be indexed on the join field(s). (It's actually quite a bit more complicated than this, but the takeaway point is that tables aren't necessarily processed left to right.)

I don't know whether MySQL does this (and now that you've asked, there goes my evening :) If it doesn't, it might in the future.

It's odd that you're finding the JOIN case to be slower from the command line. That's worth some extra examination. It shouldn't be the case.


Update: MySQL can be made to explain its query plans. See http://www.mysql.com/doc/E/X/EXPLAIN.html

In reply to Re: DBI and JOINs by dws
in thread DBI and JOINs 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.