Help Center/ TaurusDB/ Kernel/ Parallel Query/ Overview
Updated on 2026-03-24 GMT+08:00
View PDF
Share

Overview

What Is Parallel Query?

Parallel query (PQ) reduces the processing time of analytical queries to satisfy the low latency requirements of enterprise-grade applications.

It distributes a query task to multiple CPU cores for computation to shorten the query time. Theoretically, the performance improvement of parallel query is positively correlated with the number of CPU cores. The more CPU cores are used, the higher the performance improvement is.

The following figure shows the count(*) process for a table based on parallel query. Table data is divided into blocks and distributed to multiple cores for parallel computing. Each core processes some data to obtain an intermediate count(*) result, and all the intermediate results are aggregated to obtain the final result.

Figure 1 How PQ works

Scenarios

Parallel query is mainly suitable for SELECT statements to query large tables, multiple tables, and a large amount of data. This feature does not benefit extremely short queries.

  • Lightweight analysis

    The SQL statements for report queries are complex and time-consuming. Parallel query can improve the efficiency of a single query.

  • More available system resources

    Parallel query requires more system resources. You can enable parallel query to improve resource utilization and query efficiency only when the system has a large number of CPUs, low I/O loads, and sufficient memory resources.

  • Frequent data queries

    For data-intensive queries, you can use parallel query to improve query processing efficiency, ease network traffic, and reduce pressure on compute nodes.

Supported and Unsupported Scenarios

Table 1 Scenarios supported by parallel query

Supported Scenario

Description

Scans

Full table scans, index scans, index range scans, index reverse scans, index point queries, and index pushdown

Queries

Single-table queries, multi-table joins, views, subqueries, and some CTE queries, non-partitioned table queries, single-partition queries of partitioned tables, and UNION/UNION ALL queries

Joins

BNL JOIN, BKA JOIN, HASH JOIN, NESTED LOOP JOIN, SEMI JOIN, ANTI JOIN, and OUTER JOIN

Subqueries

Conditional subqueries, scalar subqueries, some correlated subqueries, non-correlated subqueries, and derived tables

Data types

Integer, character, floating-point, and time

Expression calculation

Arithmetic expressions (+, -, *, %, /, |, and &) and conditional expressions (<, <=, >, >=, <>, BETWEEN/AND, and IN)

Logical operations

OR, AND, and NOT

General functions

Character, integer, and time functions

Aggregate functions

COUNT, SUM, AVG, MIN, and MAX

CAUTION:

If you want a SQL statement containing the COUNT aggregate function to use TaurusDB parallel query, instead of InnoDB parallel query, set innodb_parallel_select_count to OFF.

Sorting, grouping, pagination, and filtering

ORDER BY, GROUP BY/DISTINCT, LIMIT/OFFSET, WHERE/HAVING, and column projection

EXPLAIN statements

EXPLAIN FORMAT=TREE, EXPLAIN ANALYZE (supported in 2.0.60.241200 and later), and EXPLAIN FORMAT=JSON introduced in MySQL 8.0 and traditional EXPLAIN statements
Table 2 Scenarios unsupported by parallel query

Unsupported Scenario

Description

Statements
  • Non-query statements (DML and DDL statements)
  • PREPARE statements
  • SELECT statements in stored procedures, functions, or triggers
  • Lock queries, such as those using the SERIALIZABLE isolation level, a FOR UPDATE lock, or a FOR SHARE lock
  • Statements for querying non-InnoDB tables, system tables, and temporary tables
  • Subqueries that cannot be converted to semi-joins
  • Statements that do not meet the ONLY_FULL_GROUP_BY rules
  • Recursive queries (WITH RECURSIVE)
  • Statements containing the WITH ROLLUP clause
  • Statements containing the HIGH_PRIORITY or SQL_BUFFER_RESULT keyword
  • 0 rows of data are returned in the point query or optimization phase, and the corresponding execution plan displays "EQ_REF, Zero limit, Impossible WHERE, Impossible HAVING, No matching min/max row, Select tables optimized away, Impossible HAVING noticed after reading const tables, no matching row in const table."
  • Queries involving ZEROFILL columns that can be optimized as constants.

Indexes
  • Spatial indexes
  • Full-text indexes

Functions
  • Window functions
  • JSON-related functions (JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_VALUE, JSON_VALID, and JSON_LENGTH)
  • Spatial functions in GIS (GeomCollection, GeometryCollection, LineString, MultiLineString, MultiPoint, MultiPolygon, and Polygon)
  • Spatial functions (ST_GeomFromText, ST_AsText, ST_Distance, ST_Intersects, ST_Contains, ST_Buffer, ST_Area, and ST_WITHIN)
  • Spatial relations (MBRContains, MBRCoveredBy, MBRCovers, MBRDisjoint, MBREquals, MBRIntersects, MBROverlaps, MBRTouches, and MBRWithin)
  • User-related functions (user, current_user, session_user, system_user, and current_role)
  • XML-related functions (ExtractValue and UpdateXML)
  • Thread ID functions (PS_CURRENT_THREAD_ID and PS_THREAD_ID)
  • Encryption and hash functions (DES_DECRYPT, SHA, SHA1, SHA2, MD5, and rand with parameters)
  • Lock and synchronization functions (get_lock, is_free_lock, is_used_lock, release_lock, release_all_locks, and sleep)
  • Functions whose return value types are not supported (UNCOMPRESS and STATEMENT_DIGEST_TEXT)
  • Some aggregate functions (BIT_AND, BIT_OR, BIT_XOR, GROUP_CONCAT, STD, STDDEV, STDDEV_POP, VARIANCE, VAR_POP, and VAR_SAMP)
  • Other system functions (set_user_var, WAIT_FOR_EXECUTED_GTID_SET, and WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS)
  • User-defined functions

Incompatibility Between Parallel Queries and Serial Queries

Number of Errors or Alarms

If an error or alarm message is displayed during serial queries, the error or alarm message will be displayed in each worker thread during the parallel queries. As a result, the total number of error or alarm messages increases.

Precision

During parallel queries, if there is a function type in a SELECT statement, additional stored procedures will be generated in the intermediate results. As a result, compared with serial queries, the precision of the floating point part in parallel queries may be different, and the final results may be slightly different.

Truncation

During parallel queries, if there is a function type in a SELECT statement, additional stored procedures will be generated in the intermediate results. In this process, the calculation result of the function needs to be cached, and data truncation may occur (generally due to data type conversion, for example, covering a floating-point value to a character string). As a result, the final result is different from the serial queries.

Sequence of Result Sets

Because tasks are executed by multiple worker threads during parallel queries, the sequence of the returned result set may not be consistent with that of serial queries. In the case of a query with LIMIT, this problem is more likely to occur. If fields of GROUP BY are invisible characters, the sequence of the returned result set is also different.

UNION ALL Result Sets

UNION ALL ignores sort operators. The sequence of the returned result set in parallel execution may be different from that in non-parallel execution. In the case of a query with LIMIT, the result sets are different.

Parent Topic: Parallel Query