Understanding MySQL explain, `rows`-wise

Question

I'm trying to figure out how should I take into account the rows column of MySQL explain's output. Here's what MySQL documentation says about it:

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

So here are my questions:

1. Regardless of its exactness, is this the number of records that are going to be examined after the indices are used or before?
2. Is it correct that I need to value the optimization of tables with high rows?
3. Is it true that the total number of records MySQL will examine is the product of rows column?
4. What are the strategies to reduce the rows?

Answer 1

1. The meaning of indexes is - that DBMS will look there first, and then use gathered information to look up matched rows. So - yes, rows will indicate how many rows will be examined after indexes were used (if they are present & applicable, of course). This question means that you are confused of what indexes are. They're not some magic, they are just real data structure. They entire sense is to reduce count of data rows used to perform query.
2. Arguable. This question can't be answered "yes" or "no". Because different tables may have different row definition - and the applied operation would be also different. Imagine that you have 100.000 rows from first table and 10.000 rows from second. But for first table you're selecting just plain value while for second table - something like standard deviation. That is: not only count of rows matters, but also what are you doing with them.
3. You may think of it as about multiplication, yes. But the thing is - it's not exact what will happen. And not exact count, of course. There is also filtered field that indicates for many rows were affected by applied conditions (like in WHERE clause). But - in general, you may estimate end result as power of 10, i.e. if you have 123.456.789 rows in first line and 111.222 in second, you may treat is as "selection of around 1E8 x 1E5" rows.
4. The techniques are quite standard and they all are about optimization of your query. First step is to take a look about how MySQL optimizes certain parts of query. Not all queries can be optimized - and in general it is too broad question, because some solutions may touch entire database and/or application structure. But understanding how to use indexes properly, what can be (and what can not be) indexed, how to create effective index - will be enough.