All Products
Search
Document Center

The query is inaccurate because the decimal field of apsaradb for MySQL is too large.

Last Updated: Nov 23, 2020

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

Problem description

The decimal field of apsaradb RDS for MySQL is too large, resulting in inaccurate query.

Causes

The decimal field size of apsaradb RDS for MySQL is too large and the query cannot be accurate, which is caused by a BUG in the MySQL database.

Fixes

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

You can solve the problem by adding an index. The SQL statement for optimization is as follows:

create table mlgtestdecimal(`campaign_id` decimal(24, 0) NOT NULL, name VARCHAR(50));
insert into mlgtestdecimal(campaign_id, name)
VALUES('XXXXXXX','XXXXXX');
insert into mlgtestdecimal(campaign_id, name)
VALUES('XXXXXXX','XXXXXX');
SELECT *
from mlgtestdecimal
where campaign_id='XXXXXXX'
  and name='XXXXXX'
  alter table mlgtestdecimal add index ind_decimal (`campaign_id`);

Application scope

  • ApsaraDB RDS for MySQL