Return number of rows looped through by MySQL when the search has a LIMIT?-CodePudding

Is it possible to have MySQL return the number of rows looked at?

SELECT * FROM `table` WHERE location like '%New York%' LIMIT 10

So in the example above, MySQL might loop through 30 rows in order to return these 10 results, this would indicate that the term "New York" is found approximately 1/3 of the time (assuming that the term is spread randomly). You could then tell the user that he can expect approx. X results before making him wait for the exact number. If there are ten million rows in the database then you could also assume that a count would take 1 million times longer than the above search and use that as the basis for a simple loading bar.

CodePudding user response：

This is kind of a hack. I wouldn't use this in production code, but only for troubleshooting.

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show session status like 'handler_read%next';
 ----------------------- ------- 
| Variable_name         | Value |
 ----------------------- ------- 
| Handler_read_next     | 0     |
| Handler_read_rnd_next | 0     |
 ----------------------- ------- 
2 rows in set (0.01 sec)

mysql> select * from mytable where location like '%New York%' limit 10;
 ---- ---------- 
| id | location |
 ---- ---------- 
|  1 | New York |
|  3 | New York |
|  4 | New York |
|  6 | New York |
|  8 | New York |
|  9 | New York |
| 13 | New York |
| 14 | New York |
| 17 | New York |
| 28 | New York |
 ---- ---------- 
10 rows in set (0.00 sec)

mysql> show session status like 'handler_read%next';
 ----------------------- ------- 
| Variable_name         | Value |
 ----------------------- ------- 
| Handler_read_next     | 0     |
| Handler_read_rnd_next | 17    |
 ----------------------- -------

This shows the number of times the handler API was invoked to read the next row. Handler_read_rnd_next indicates requests to read the next row during a table-scan (as would be used for your LIKE search with wildcards). Handler_read_next indicates requests to read the next row in index order when using an index.

Remember to use flush status to reset the counters to 0 before each query you want to measure.

CodePudding user response：

Maybe this can be helpful

SELECT count(location ) FROM table WHERE LOWER(location) LIKE "%New York%" GROUP BY location