MySQL Forums
Forum List  »  Newbie

Re: Optimizing GROUP BY
Posted by: Rick James
Date: December 23, 2008 10:45PM

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.

Options: ReplyQuote

Written By
December 23, 2008 02:11AM
December 23, 2008 11:43AM
Re: Optimizing GROUP BY
December 23, 2008 10:45PM
December 24, 2008 02:54AM
December 24, 2008 03:05AM
December 24, 2008 05:20PM
December 24, 2008 04:13AM
December 24, 2008 05:27PM
December 24, 2008 12:25PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.