Is there a way to get row_examined in MySQL without a slow log?

Question

I am creating some profile information for a home application. I would like the debug page to display a request sent along with how many lines were checked, not assuming slow_log is enabled, not to mention parsing it.

Back in 2006, I wanted it failed . Is this still true?

I see that Peter Zaitsev has a technique where you:

• Run FLUSH STATUS;
• Run the query.
• Run SHOW STATUS LIKE "Handler%";

and then on output:

Handler_read_next = 42250 means that 42250 lines were analyzed during this scan

which sounds as if MySQL was just learning indexes, it should give you a number. But is there a set of status vars that you can poll, add and find out how many lines are checked? Any other ideas?

Answer 1

This is slightly better than in 2006. You can publish SHOW SESSION STATUS before and after, and then look at each of the Handler_read_ * counters to find out the number of rows checked.

. , , , . , MySQL Query Analyzer, , SHOW SESSION STATUS / ( , SHOW SESSION STATUS , ).

, , , _examined. , :

http://www.percona.com/docs/wiki/patches:microslow_innodb#changes_to_the_log_format

"Disk_tmp_table: Yes" "Disk_filesort: Yes".

Answer 2

5.6.3, MySQL performance_schema , performance_schema.events_statements_current.

, , "ROWS_EXAMINED".

http://dev.mysql.com/doc/refman/5.6/en/events-statements-current-table.html

.

http://dev.mysql.com/doc/refman/5.6/en/statement-summary-tables.html

Answer 3

:

Handler_read_rnd

. , , . , , MySQL , , .

Handler_read_rnd_next

. , . , , , .

read_rnd* fullscan.

, , , - .

, :

CREATE TABLE mytable (id INT NOT NULL PRIMARY KEY, data VARCHAR(50) NOT NULL)

INSERT
INTO    mytable
VALUES  …

SELECT  id
FROM    mytable
WHERE   id BETWEEN 100 AND 200

SELECT  *
FROM    mytable
WHERE   id BETWEEN 100 AND 200

the last two queries will return 1to read_key, 101in read_nextand 0both in read_rnd, and read_rnd_next, despite the fact that the actual string search occurs in the second query.

Answer 4

Prepare the request using EXPLAIN. In MySQL, which will show the query execution path, which tables were checked, as well as the number of rows checked for each table.

Here is the documentation .