MySQLism: avoid nested ORDER BY, use nested queries
Drupal 7 will no longer be supported after January 5, 2025. Learn more and find resources for Drupal 7 sites
SQL-99 allows nested queries, but forbids nested ORDER BY. The reason is that nested queries improve the ability of the parser to understand the query.
Nested ORDER BY clauses works under MySQL but fail under PostgreSQL:
(SELECT thread
FROM comments
WHERE nid = 49805 AND status = 0
ORDER BY timestamp DESC LIMIT 326)
ORDER BY thread DESC LIMIT 1
The correct SQL query to execute under both environments should be:
SELECT thread FROM
(SELECT thread
FROM comments WHERE nid =49805 AND status = 0
ORDER BY timestamp DESC LIMIT 326)
AS thread ORDER BY thread DESC LIMIT 1
There is no speed issue in replacing nested ORDER BY with nested queries. In MySQL and PostgreSQL, the parser will rewrite the queries to execute as nested queries.
References:
http://drupal.org/node/396388
Help improve this page
You can:
- Log in, click Edit, and edit this page
- Log in, click Discuss, update the Page status value, and suggest an improvement
- Log in and create a Documentation issue with your suggestion