Hi Peter,
> 1: Sometimes "explain $query" shows that an index is used (all key columns are
> NOT NULL), when in fact "$query" shows up in the slow log exactly iff
> log-queries-not-using-indexes is set in /etc/mysql/my.cnf. (See below for
> example) Why is that?
It shows up there because type=index, which means that you do an index scan which basically means reading all index entries. This is a bit confusing; index_scan would be a better name for it.
The most important part of EXPLAIN is the type column. If it shows eq_ref, ref or range (index lookup types) or system/const you're good in most cases. If it shows ALL or index all rows are read.
> But for the exact same structure in a "small" database [5] with "few" rows,
> explain tells me that a different set of keys will be used:
The responsibility of the optimizer is to take a great number of information into consideration and then come up with a good plan for execution (the order of which tables are accessed, which access method is used for each table etc). Some important info for the optimizer is:
* the number of rows in the tables
* selectivity of indexes, given the conditions of the query
* whether or not an index provides required ordering for free
Thus, the best access method for a table with few rows may not be the best access method after a bunch of new rows have been inserted.
Have you read the EXPLAIN Output Format documentation page [1]? If not, you should :)
[1]
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com