very huge mysql request

[Anonymous Monk]

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

[cees]

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

[Corion]

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.

[Fletch]

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

[socketdave]

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'.

[CountZero]

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.

[Anonymous Monk]

Thanks a lot, LIMIT 1 is the solution!!!!!!