Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: Big database queries

by beebware (Pilgrim)
on Sep 02, 2002 at 05:18 UTC ( [id://194527]=note: print w/replies, xml ) Need Help??


in reply to Big database queries

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?

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://194527]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (7)
As of 2024-03-28 15:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found