Skip to main content

Explicitly Disable Sorting of Result Set

· 2 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

Copyright © 2024 PawSQL

Problem Definition​

In earlier versions of MySQL, GROUP BY would default to sorting by the grouping fields even without an ORDER BY clause. This could cause unnecessary file sorting which impacts SQL query performance. Adding ORDER BY NULL forces disabling sorting on the result set.

For example in the following:

SELECT l_orderkey, sum(l_quantity) 
FROM lineitem
GROUP BY l_orderkey;

In MySQL 5.x, GROUP BY l_orderkey would cause default sorting, which can be avoided by adding ORDER BY NULL:

SELECT l_orderkey, sum(l_quantity)
FROM lineitem
GROUP BY l_orderkey
ORDER BY NULL;

PawSQL Rewrite​

Conditions​

  • MySQL database, version below 8.0
  • GROUP clause without SORT clause

Database Types and Versions​

  • MySQL, versions below 8.0

In MySQL 8.0, GROUP BY itself does not sort on the grouping fields, so GROUP BY l_orderkey can be used directly without needing ORDER BY NULL.

About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The database types supported include MySQL, PostgreSQL, Oracle, and openGauss; and the products provided by PawSQL include:

  • PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
  • PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.
  • PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, can be installed and deployed independently, and provides SQL optimization services through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.

Contact Us​

Website: https://docs.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql