consistent_order for MariaDB Xpand
When using the Xpand Storage Engine topology, the details described here only apply when you connect to the Xpand nodes.
Consistent ordering in Xpand guarantees that rows returned from repeated runs of the same query are always in the same order. If a query does not specify an explicit
ORDER BY and the
consistent_order global variable is set to false, the order in which rows are returned is not guaranteed to be the same for repeated executions.
The internal storage implementation for Xpand is different from MariaDB Server. Since Xpand is a distributed database, the ordering of result sets will not be the same as those from MariaDB. If an application's behavior depends on how the rows are ordered, specify an explicit
ORDER BY clause. Consistent ordering will also apply when non-unique results are returned from ORDER BY.
Enabling Consistent Ordering
Consistent ordering can be specified per session or globally. To enable consistent ordering, set the value for
TRUE after connecting to the socket for the MariaDB Xpand service.
INSERT INTO foo VALUES (0),(1),(2),(3);
SELECT * FROM foo;
+----+ | id | +----+ | 2 | | 1 | | 4 | | 0 | +----+
SET session consistent_order = TRUE; SELECT * FROM foo;
+----+ | id | +----+ | 0 | | 1 | | 2 | | 3 | +----+
Consistent Order may also be set globally.
SET global consistent_order = TRUE;
Overhead of Consistent Ordering
Enabling consistent ordering increases query performance overhead because Xpand needs to:
Determine the appropriate ordering.
Perform a secondary compilation after the appropriate ordering is determined.
Return the query results in a specific order versus in-parallel, and thus very quickly.
The cost of enabling consistent ordering is that each query will take approximately twice as long to compile and will incur a performance penalty, as well. Each deployment's workload differs, so the best way to determine the specific overhead on your deployment is by inspection.
Consistent Ordering on Joins
Consistent ordering requires the plan for a query to include steps that ensure the order. Such queries take longer to plan and compile. Queries that do a large number of joins have a higher compilation time, and should use explicit ordering instead.
One notable exception are aggregate queries for which the
ORDER BY and
GROUP BY specify the same ascending columns. Such queries do not incur the same planner impact.