SQL_CALC_FOUND_ROWS issue

[s6930064]

Based on Rob post: http://fabrikar.com/forums/index.php?threads/bug-distinct.39160/#post-196721

I would like to point out that the use of SQL_CALC_FOUND_ROWS/ FOUND_ROWS is canceling query limits at ?finesseData?.

At http://dev.mysql.com/doc/refman/5.7/en/information-functions.html you can read that ?A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward.?

Also from https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ maybe in general is better to use count(*) instead of FOUND_ROWS.

I did some tests with Fabrik large lists and I found that if I removed SQL_CALC_FOUND_ROWS I gain x10 execution speed. I also tried creating covered indexes but did not speed Fabrik lists.

I would like to propose to give an option to users to use SQL_CALC_FOUND_ROWS or count(*) for finding out total records.

 

[cheesegrits]

Are you merging joined data or not?

-- hugh

 

[s6930064]

no merging of join data.

 

[s6930064]

I did a small mod on /com_fabrik/models/list.php code by removing the ?SQL_CALC_FOUND_ROWS? at line 2687 and replaced ?FOUND_ROWS ?at lines 962,963 with:

$db = $this->getDb();
$table = $this->getTable();
$query = $db->getQuery(true);
$query->select('COUNT(*) AS t')
->from($table->db_table_name);
$query = $this->buildQueryJoin($query);
$query = $this->buildQueryWhere($this->app->input->getBool('incfilters', true), $query);
$query = $this->buildQueryGroupBy($query);
$totalSql = (string) $query;
$totalSql = $this->pluginQuery($totalSql);
$db->setQuery($totalSql);
$ret = $db->loadResult();
$this->totalRecords = $ret;

This way (remove SQL_CALC_FOUND_ROWS) I was able to speed my large list from 5secs to 0.0008sec.

I do not know if I missed something but until a better solution is provided by Fabrik team I have to use my mod to address the performance issues.