All Products
Search
Document Center

:Apsaradb RDS for MySQL supports wrong sorting and paging for data queries

Last Updated:May 20, 2022

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

For example, the alarm_test table structure is as follows:

CREATE TABLE alarm_test (   id bigint(20) NOT NULL DEFAULT '0',   detail varchar(255) CHARACTER SET utf8 NOT NULL,   created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Sort the data by the value of the created_on field and return the first 10 rows, as shown in the following figure.

created_on前10

Sort the rows by the value of the created_on field and take 10 rows starting from the 11th row. As shown in the following figure, the data obtained from the two sort operations is overlapped and unordered.

created_on11

Cause

Sorting and paging results will vary according to the situation, and the results are unpredictable. The value of the created_on field is the same as that of the 20 rows in the first 21 rows.

Solution

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.

There are two ways to sort the sorting fields in an orderly manner.

  • Modify the collation and add a primary key field to make sure that the sorting fields do not contain duplicate records. The SQL statement is as follows.
    select id, created_on from alarm_test order by created_on, id limit 0,10;
    select id, created_on from alarm_test order by created_on, id limit 10,10;
    The following command output is returned.
    加入主键字段
  • Add an index to the sort field where duplicate values occur, as shown below.
    CREATE TABLE alarm_test_idx (
    id bigint(20) NOT NULL DEFAULT '0',
    detail varchar(255) CHARACTER SET utf8 NOT NULL,
    created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY created_on (created_on)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    Note: We recommend that you add indexes to provide predictable results and improve query execution efficiency, as shown in the following figure.添加索引

Application scope

  • ApsaraDB RDS for MySQL