Recommended Servers for MySQLThe world's most popular open source database
- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 7.2 Optimizing SELECT and Other Statements
- 7.2.1 Optimizing Queries with EXPLAIN
- 7.2.2 Estimating Query Performance
- 7.2.3 Speed of SELECT Queries
- 7.2.4 WHERE Clause Optimization
- 7.2.5 Range Optimization
- 7.2.6 Index Merge Optimization
- 7.2.7 Condition Pushdown Optimization
- 7.2.8 IS NULL Optimization
- 7.2.9 LEFT JOIN and RIGHT JOIN Optimization
- 7.2.10 Nested Join Optimization
- 7.2.11 Outer Join Simplification
- 7.2.12 ORDER BY Optimization
- 7.2.13 GROUP BY Optimization
- 7.2.14 DISTINCT Optimization
- 7.2.15 Optimizing IN/=ANY Subqueries
- 7.2.16 LIMIT Optimization
- 7.2.17 How to Avoid Table Scans
- 7.2.18 INFORMATION_SCHEMA Optimization
- 7.2.19 Speed of INSERT Statements
- 7.2.20 Speed of UPDATE Statements
- 7.2.21 Speed of DELETE Statements
- 7.2.22 Other Optimization Tips
The most general way to satisfy a GROUP BY
clause is to scan the whole table and create a new temporary
table where all rows from each group are consecutive, and then
use this temporary table to discover groups and apply aggregate
functions (if any). In some cases, MySQL is able to do much
better than that and to avoid creation of temporary tables by
using index access.
The most important preconditions for using indexes for
GROUP BY are that all GROUP
BY columns reference attributes from the same index,
and that the index stores its keys in order (for example, this
is a BTREE index and not a
HASH index). Whether use of temporary tables
can be replaced by index access also depends on which parts of
an index are used in a query, the conditions specified for these
parts, and the selected aggregate functions.
In MySQL, GROUP BY is used for sorting, so
the server may also apply ORDER BY
optimizations to grouping. See
Section 7.2.12, “ORDER BY Optimization”.
There are two ways to execute a GROUP BY
query via index access, as detailed in the following sections.
In the first method, the grouping operation is applied together
with all range predicates (if any). The second method first
performs a range scan, and then groups the resulting tuples.
User Comments
GROUP BY eliminates ORDER BY so here is my solution:
table name: prices
columns: id, id_something, price, from_what_date
SELECT * FROM prices WHERE from_what_date <="2007-01-01" GROUP BY id_something ORDER BY id_something, data desc;
WILL NOT WORK if you have more prices for "something" changed in time. It will work the same as :
SELECT * FROM prices WHERE from_what_date <="2007-01-01" GROUP BY id_something ORDER BY id_something;
MySQL will take the price written first ( with lowest id i think )
What to do ? First ORDER BY, next GROUP BY
SELECT * FROM
( SELECT * FROM prices WHERE from_what_date <="2007-01-01" ORDER BY data desc ) as prices
GROUP BY id_something ORDER BY id_something;
and ...
SELECT * FROM prices WHERE from_what_date <="2007-01-01" ORDER BY id_something, data desc GROUP BY id_something;
WILL NOT WORK too, because of wrong statement.
Isn't it a bug ?
It may be a bug, or more likely just a caveat of the system.
It would really be great to be able to use ORDER BY *before* GROUP BY without having to use that nested SELECT statement. But at least the nested statement provides a way to do what is needed in cases like this. And who knows - given the way MySQL's queries are internally handled, it may be more efficient to use the nested select than to implement an ORDER BY -> GROUP BY method.
Add your own comment.