TencentDB for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Database Architecture
- Database Instance
- Kernel Features
- Kernel Version Release Notes
- Functionality Features
- Performance Features
- Parallel Query
- Security Features
- Stability Features
- Purchase Guide
- Getting Started
- Connecting to MySQL Instance
- Database Audit
- Audit Rule Template
- MySQL Cluster Edition
- Maintenance Management Instance
- Operation Guide
- Instance Management and Maintenance
- Instance Upgrade
- CPU Elastic Expansion
- Read-Only/Disaster Recovery Instances
- Database Proxy
- Database Proxy Kernel Features
- Database Proxy Management
- Automatic Read/Write Separation Feature
- Connection Pool Feature
- Other Features
- Account Management
- Database Management Center (DMC)
- Parameter Configuration
- Network and Security
- Backup and Rollback
- Restoring Database from Backup File
- Monitoring and Alarms
- Operation Logs
- Practical Tutorial
- Calling MySQL APIs in Python
- White Paper
- Performance White Paper
- Performance Test Report
- Troubleshooting
- Connections
- API Documentation
- Data Import APIs
- Rollback APIs
- Parameter APIs
- Making API Requests
- Instance APIs
- Database Proxy APIs
- Database Audit APIs
- Security APIs
- Account APIs
- Backup APIs
- Database APIs
- Monitoring APIs
- Log-related API
- FAQs
- Backup
- Connection and Login
- Instance Upgrade
- Performance and Memory
- Service Agreement
- Reference
Computation Pushdown
Last updated: 2024-07-22 12:37:15
Overview
This feature pushes LIMIT/OFFSET and SUM operations down to the storage engine InnoDB when querying single tables, effectively reducing query latency.
When LIMIT/OFFSET is executed using secondary indexes, this feature can avoid using the clustered index values as pointers to find the full table rows, effectively cutting the cost of scanning table data.
This feature pushes SUM operations down to InnoDB. In other words, instead of sending rows to the MySQL server, InnoDB calculates data itself and returns the final result to the MySQL server.
Supported Versions
LIMIT/OFFSET optimization applies to kernel version MySQL 5.7 20180530.
SUM optimization applies to kernel version MySQL 5.7 20180918.
Use Cases
This feature is mainly used to optimize single-table queries with LIMIT/OFFSET or SUM clauses, such as
Select *from tbl Limit 10”, “Select* from tbl Limit 10,2, and Select sum(c1) from tbl.This feature cannot optimize the following queries:
Queries with DISTINCT, GROUP BY, or HAVING clauses
Nested subqueries
Queries with FULLTEXT indexes
Queries with ORDER BY clauses, where the optimizer fails to use indexes to implement ORDER BY
Queries with multi-range read (MRR)
Queries with SQL_CALC_FOUND_ROWS.
Performance Data
Import one million rows of data and test query performance in sysbench:
The execution time of
select * from sbtest1 limit 1000000,1; decreases from 6.3 to 2.8 seconds.The execution time of
select sum(k) from sbtest1; decreases from 5.4 to 1.5 seconds.Instructions
During the execution of an SQL statement, the optimizer automatically modifies the query execution plan to implement computation pushdown according to the following parameters.
Parameters are as follows:
Parameter | Effective Immediately | Type | Default Value | Valid Values/Value Range | Description |
cdb_enable_offset_pushdown | Yes | bool | ON | {ON,OFF} | Enable or disable LIMIT/OFFSET pushdown. It is enabled by default. |
cdb_enable_sumagg_pushdown | Yes | bool | OFF | {ON,OFF} | Enable or disable SUM pushdown. It is disabled by default. |
Note:
Currently, you cannot directly modify the values of the above parameters. If needed, submit a ticket for assistance.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No