Re: Optimizing GROUP BY

[Rick James]

GROUP BY x ORDER BY NULL -- this is _sometimes_ an optimization over just the GROUP BY. GROUP BY guarantees ordering (non-standard), ORDER BY NULL disables that (in the rare cases where it matters.

Please show your real query, not a bogus one -- there are MANY differences in what goes on. Please include SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN.

In InnoDB, the data is ordered by PRIMARY KEY. (If you don't have a PK, a UNIQUE key might be picked for PK, or a BIGINT will be provided.) The data (and PK) are BTree structured. Meanwhile, each secondary key is in a separate BTree where the leaf nodes have the corresponding PK field(s).

Therefore, fetching by a secondary key (InnoDB only) usually involves two BTree lookups. In general this is not bad.

"Using where" does not say much. "Using index" is beneficial -- it means that all the necessary fields were found in a secondary index, so the data did not have to be fetched.