QOT

Last week (or rather last weekend) was quite productive. I was able to make a good progress migrating to the new mysql-test based testing framework. Reviewing the tests I was able to find and fix some bugs. For example there was a bug that ignored some columns while detecting index capabilities. As I mentioned earlier I also started to rework the output format. Here’s an example:

/*

Query:

select sum(col2) from t2 group by col1selectivity:

all rows

used tables:

t2 (all rows)

used aggregate functions:

sum ( col2 )

ordering:

implicit

related existing indexes:

t2.ix123(col1, col2, col3): covering, optimizes ORDER BY, optimizes GROUP BY

*/

As you can see now the tool reports used aggregate functions, if any. This is very convenient for manual query analysis, especially in non-trivial cases.

Next, the “ordering” now can be “implicit”. This is reported for the cases when there’s a GROUP BY clause and no ORDER BY clause.  As you remember in such cases MySQL automatically adds ordering by the columns used in the GROUP BY. This is a non-standard behavior and in some cases it could hurt the performance so I decided to add this reminder (If you want to disable implicit ordering add ORDER BY NULL to the query).

Next, as you might have noticed I changed index information output format. Below the “related existing indexes” the tool prints information about all the indexes that can be used by MySQL server to execute this query. For every index the tool specifies how the index can be used with thhis query. The following options are possible:

“lookup” - the index can be used to fully resolve the WHERE clause (if any);
“lookup (partial)” - can be used to resolve the WHERE only partially.  This could be because the index doens’t contain all the fields used in the WHERE clause, or the fields order in the index doesn’t match the order required by the WHERE clause. In general this means that MySQL is unlikely to use this index unless it has a very high estimated selectivity. Both “lookup” and “lookup (partial)” indexes are listed under the “possible keys” section in the output of EXPLAIN SELECT command.
“covering” - means the entire query can be resolved using this index (without table or other index access at all);
“optimizes ORDER BY” - means MySQL can use this index to avoid filesort in ORDER BY;
“optimizes GROUP BY” - means MySQL can use this index to avoid temporary table creation in GROUP BY;

So far that’s it. See you next week