Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

very huge mysql request

by Anonymous Monk
on Jan 19, 2006 at 14:24 UTC ( [id://524230]=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I need to do a mysql request which returns thousends of rows. However I only need the first row. Which means my perl-script connects to the database, gives it the request, waits for 10 seconds and only retrieves the first element:
my $sth = $dbl->prepare($command) ; $sth->execute() ; my @row = $sth->fetchrow_array ; $sth->finish ;
How can I change this request such that mysql only takes the first match and will spend less time thinking ? ( I need to do this request > 100000 times => it will take 278 hours ==> 11 days....)

Thanks a lot in advance
Luca

Replies are listed 'Best First'.
Re: very huge mysql request
by cees (Curate) on Jan 19, 2006 at 14:25 UTC

    Check out the MySQL documentation for the LIMIT clause which will limit the number of results that are returned for a given query.

Re: very huge mysql request
by Corion (Patriarch) on Jan 19, 2006 at 14:27 UTC

    Rewrite your SQL statement so that it only returns the first row. Most likely this can be done by appending LIMIT 1 to your query. Otherwise, MySQL will do far more work than what is necessary. You want to make sure there is an ORDER BY ... clause in your SQL too, so your first row is a well-defined result.

    Also consider instead of doing a loop which repeats the same query 100000 times with different parameters, combine that loop into one query which returns all 1000000 resultsets.

Re: very huge mysql request
by Fletch (Bishop) on Jan 19, 2006 at 14:26 UTC

    Not a Perl solution, but look at LIMIT in the documentation for SELECT.

Re: very huge mysql request
by socketdave (Curate) on Jan 19, 2006 at 14:26 UTC
    Can you 'sort by' and 'limit' your result to make mysql just return one row?

    EDIT: That should have been 'order by', not 'sort by'.
Re: very huge mysql request
by CountZero (Bishop) on Jan 20, 2006 at 07:25 UTC
    The LIMIT 1 suggestions are of course good, but a combination with sorting is perhaps not so very good. Depending on the indexes you have defined on your tables and fields, running a query which need to be sorted will --in a worst case scenario-- still first get the whole resultset, sort it and then drop all of them except the first record (due to the LIMIT 1 clause).

    In similar problems I most always found that rewriting the SQL to natively return only one record (without needing to do a sorting operation followed by a LIMIT 1) gave me generally better performance.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: very huge mysql request
by Anonymous Monk on Jan 19, 2006 at 14:29 UTC
    Thanks a lot, LIMIT 1 is the solution!!!!!!

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2024-12-03 04:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found