Lasso Soft Inc. > Home

[found_rows]

Linkfound_rows
AuthorJohan Solve
CategoryDatabase
Version8.x
LicensePublic Domain
Posted09 Jun 2006
Updated24 Jan 2007
More by this author...

Description

When using -uselimit in a MySQL search, [found_count] will return the value of -maxrecords (at most) instead of the true count of found records.

This tag always returns the true count of found records also when using -uselimit in an inline, as well as when using LIMIT in an -sql inline. Use the tag as a replacement for [found_count].

The tag is only intended for MySQL versions 4.0 or higher. The tag requires Lasso 8.0 or later. 

Lasso 8.5 and later returns the correct value for found_count when using -uselimit with regular inlines, but when using -sql inlines this tag is still  needed to get the correct count of found records when having LIMIT in the query.

Credit to Jason Huck for ideas for this tag.

Update: changed default method to COUNT(*) since SQL_CALC_FOUND_ROWS appears to be much slower.

Sample Usage

inline: -database='mydatabase',
	-table='mytable',
	-op='gte', 'id'=22,
	-maxrecords=10,
	-skiprecords=50,
	-uselimit,
	-search;
	found_rows + ' found records';
/inline;

Source Code

Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.

> ' LIMIT ';
    // found_count must be accurate
    return: found_count;
  /if;
  if: #sql !>> ' GROUP BY ';
    // Default method, usually the fastest. Can not be used with GROUP BY for example. 
    #sql = 'SELECT COUNT(*) AS found_rows ' + #sql -> (substring: (#sql -> (find: ' FROM ')) + 1) ;
    #sql = (string_replaceregexp: #sql, -find='LIMIT\\s[0-9,]+', -replace='');
  else; // query contains GROUP BY so use SQL_CALC_FOUND_ROWS which can be much slower, see http://bugs.mysql.com/bug.php?id=18454
    #sql -> (removeleading: 'SELECT ');
    #sql = 'SELECT SQL_CALC_FOUND_ROWS ' + #sql + ';SELECT FOUND_ROWS() AS found_rows';
    #sql = (string_replaceregexp: #sql, -find='LIMIT\\s+[0-9,]+', -replace='LIMIT 1', -ignorecase);
  /if;
  inline: -sql=#sql;
    if: (field: 'found_rows') > 0;
      return: integer: (field: 'found_rows'); // exit here normally
    /if;
  /inline;
  // fallback
  return: found_count;
/define_tag;


?>

Related Tags

Comments

24 Jan 2007, Johan Solve

Tag updated

I updated the tag to use COUNT(*) instead of SQL_CALC_FOUND_ROWS as long as the query does not contain GROUP BY. Hopefully this will make the tag much faster when possible.

24 Jan 2007, Johan Solve

Performance warning

A bit of warning when using this tag - SQL_CALC_FOUND_ROWS can be really slow, in many situations COUNT(*) is several times faster. See for example http://bugs.mysql.com/bug.php?id=18454

However, for complex queries for example with GROUP statements it can be difficult to use COUNT(*) to return a proper result.

Please log in to comment

Subscribe to the LassoTalk mail list

LassoSoft Inc. > Home

 

 

©LassoSoft Inc 2015 | Web Development by Treefrog Inc | PrivacyLegal terms and Shipping | Contact LassoSoft