Why would the query optimiser show rows scanned = total number of matched rows despite filtering on an indexed column with a limit applied?

The Rows in EXPLAIN

• is an estimate, often a very crude estimate; and
• usually does not take into account LIMIT.

Query 1 -- The query was performed using just the index's BTree. (clue: "Using index"). It probably touched only 10 rows. (No clue available in Explain)

Query 2 -- Full table scan (Clues: All and Nulls). It read all the rows, but may have delivered only a fraction of the rows (Clue: Filter 10.00% -- again a crude estimate)

Query 3 -- Hmmm... It picked the wrong [my opinion] way to perform the query; it decided to use the index without realizing that it might have to go through the entire table to find 10 rows. INDEX(field2, field1), in this order would be optimal.

Query 4 -- (Perhaps you typed it wrong? The ORDER BY is unnecessary since the output has all rows with the same value.)

Other helpers:

• If you want to know exactly how many rows were touched, do this:

  FLUSH STATUS;
  SELECT ... ;
  SHOW SESSION STATUS LIKE 'Handler%';
  

• EXPLAIN FORMAT=JSON SELECT ...

• The "Optimizer trace".

• The slowlog records "rows examined".

• Index Cookbook (It includes the 'Handler' and Optimizer Trace tips.)

Perhaps the following addresses your final question. If there are no matching rows in the table, there still has to be a probe to discover that fact. That increases some actual row counts by 1, including 0 to 1. Similarly, if ther are only 6 rows matching the WHERE (and less than the LIMIT), it will 'examine' 7 rows to realize it is time to stop.