Number of rows in result interface
Closed this issue · 6 comments
Feature
The current interface does not indicate the number of lines of a "select" result.
It could be useful to retrieve this number (like mysql_num_rows) without doing new request or counting pages / going to last page.
cocoto, that could be useful feature, but... for large result sets that could significantly increase query time, as MySQL wouldn't stop by LIMIT and should select all rows to count them.
Any ideas how to avoid such overhead?
I didn't noticed that you include a LIMIT in queries before executing them.
Since MySQL 4.0, we can use the FOUND_ROWS function.
http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html
But it requires a new (light) request, which is probably not ideal.
Performances seem to be preserved, but I didn't benchmark them at all
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
yes, input SQL analysed and if it's "SELECT" and no "LIMIT" included - it will add it's own LIMIT depending on page we need to show for the user.
And actually number of pages not counted, so paging navigator is infinite (but almost nobody noticed that :). That's why it's faster compared to phpMyAdmin or others which do rows count.
Well, according to mysqlperformanceblog.com link you provided "count+select" faster than "select SQL_CALC_FOUND_ROWS", but only in case if there are proper indexes.
As queries entered by user could be any, we cannot assume that indexes would be used.
In general case (Full-scan) and large number of rows in table - calculating rows count could take significant time.
I just did a test on table with about 2000000 of records:
0.12s - select * from table LIMIT 50
38.48s - select SQL_CALC_FOUND_ROWS * from table LIMIT 50 + select ROWS_FOUND
14.65s - select count(*) from table + select * from table LIMIT 50
So, calculating total number of rows could be 100x slower, that's why I didn't implemented it.
I have an idea how to allow user get counts quickly and still have quick pagination:
I can add "Count" button near "Go", it will execute user's query and return only count of result set.
That's the same as if user write "select count(*) ..." instead of "select *...", but at least it would be easier.
It could be easier but considering multiple factors (like DISTINCT keyword, which can be executed after multiple operations and selections), I think it's better to let users express their "counting queries".
Or probably doing a "count(*) from (INPUT_QUERY)" ?
yes, there are many things to think about, that's why I don't add many features to the script.
But for count button I think it's possible to wrap input query into subquery.
However, it will work only for "SELECT queries", so it will be necessary to analyse query and if it's not start with "select", probably some js alert need to be displayed instead of executing.
done in 1.9.141219 using select count(*) from (INPUT_QUERY) for queries that display pagination