very huge mysql request

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 ;
[download]

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

Comment on very huge mysql request Download Code

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.	[reply]
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.	[reply] [d/l] [select]
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`.	[reply]
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'.	[reply]
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	[reply] [d/l] [select]
Re: very huge mysql request by Anonymous Monk on Jan 19, 2006 at 14:29 UTC
Thanks a lot, LIMIT 1 is the solution!!!!!!	[reply]


There's more than one way to do things
	PerlMonks