You say it returns 60 million rows - how many rows were you expecting it to return? If you were only expecting aroun d100 - then you've probably got a incorrect JOIN causing a caesterian (sp?) loop thingy (where it matches up every row in one table in every row in another). The LIMIT statement may be a good idea as well, so may ensuring the columns your are SELECTing on are index columns (ok, it won't reduce the number of rows returned [or, it _shouldn't_ reduce], but it may decrease the database machine usage levels).

What are you trying to achieve with such a big SELECT anyway? If you are meant to be handling 60million rows - have you got enough RAM in _BOTH_ machines: if it's only a 1k per row of data, you'll be looking at a _bare minimum_ recommendation of 60million X 1k X 3 for the database machine (171_Gb_ of RAM). Why those figures? Well, to allow the DB to load in the tables, compile the information, squirt it out and temporary space in between. If you have less than that amount of RAM, they'll probably be a _hell_ of a lot of disc-thrashing as data is moved to the swap area and back again.

What is your exact SQL statement?

In reply to Re: Big database queries by beebware
in thread Big database queries by ezekiel

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.