Run speed difference between {between} and {gte, lte} functions in Sequelize

Question

First of all, please understand that the sentence may be strange because of the lack of English skills. If there's a weird sentence, I'll explain it in detail!

Improved one time-consuming feature. Improvements have increased the speed of this feature by 40%. (reduced from approximately 5 seconds to 3 seconds)

The environments for this project are JavaScript, node.js, Sequelize, and mysql.

First, the existing code is as follows.

const startDate = moment(params.searchDate).format(‘YYYY-MM-DD 00:00:00’)
const endDate = moment(params.searchDate).format(‘YYYY-MM-DD 23:59:59’)

await receive.findAll({
  include: [
    {
      attributes: [‘parking’, ‘parkingNm’, ‘parkingDetail’, ‘section’, ‘startDatetime’, ‘endDatetime’, ‘createdAt’],
      model: models.movement,
      separate: false,
      order: [[‘createdAt’, ‘desc’]],
    },
  ],
  where: {
    [Op.or]: [
      {
        realUsetime: {
          [Op.between]: [startDate, endDate],
        },
      },
      {
        useDatetime: {
          [Op.between]: [startDate, endDate],
        },
      },
    ],
  },
})

Secondly, the improvement code.

const startDate = moment(params.searchDate).format(‘YYYY-MM-DD 00:00:00’)
const endDate = moment(params.searchDate).format(‘YYYY-MM-DD 23:59:59’)

await receive.findAll({
  where: {
    [Op.or]: [
      {
        realUsetime: {
          [Op.gte]: startDate,
          [Op.lte]: endDate,
        },
      },
      {
        useDatetime: {
          [Op.gte]: startDate,
          [Op.lte]: endDate,
        },
      },
    ],
  },
})

I removed unnecessary table joins from this logic and changed the function {between} to {gte,lte}. (In fact, since it is an inquiry through the calendar date range, we used gte (start date) and lt (start date + 1D) for accurate data inquiry. However, for variable control, gte and lte were used in the actual test, and the result values are inquired the same due to the data format of the database.)

I thought the lookup speed was improved by removing unnecessary table joins. However, setting the same table join in both queries for testing or removing the table join did not result in the same speed. Therefore, I did not think that the performance improvement due to table joining was significant.

Next, I thought there was a performance difference in the database due to the {between}, {gte, lte} difference due to queries running on the database. However, running the actual query in the database did not make much difference in speed, and the export showed that the execution plan was the same.

The reason for the performance difference after this test was confirmed through the following test. When you changed the improvement code's {gte,lte} to the existing code's {between}, you saw that the speed became the same.

In conclusion, there is no difference in the performance of {between} and {gte, lte} running on the database, but we thought it occurred between the intervals requested by Sequelize to the database. (For example, problems encountered when Sequelize queries that code…)

I'm wondering if there's a performance difference between {between} and {gte, lte} in Sequelize, not database queries.

If it's not the difference that happens in this case, I don't think I can find a clear reason for how I improved this code.

==================================================

The additional requested CREATE SHOW TABLE and the actual SQL statement of both codes.

1-1. receives TABLE

CREATE TABLE `receives` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `receive_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `status` int(11) NOT NULL,
 `phone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `use_datetime` datetime DEFAULT NULL,
 `real_usetime` datetime DEFAULT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `deleted_at` datetime DEFAULT NULL
 PRIMARY KEY (`uid`),
 UNIQUE KEY `receive_no` (`receive_no`),
 KEY `search_index` (`car_number`,`valet_type`,`phone`,`created_at`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1-2. movements TABLE

CREATE TABLE `movements` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `parking` int(11) DEFAULT NULL,
 `parking_detail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `section` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `start_datetime` datetime NOT NULL,
 `end_datetime` datetime NOT NULL,
 `receive_uid` int(11) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `deleted_at` datetime DEFAULT NULL,
 PRIMARY KEY (`uid`),
 KEY `receive_uid` (`receive_uid`),
 CONSTRAINT `movements_ibfk_1` FOREIGN KEY (`receive_uid`) REFERENCES `receives` (`uid`) ON DELETE NO ACTION ON UPDATE CASCADE,
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2-1. generated SQL of existing code

SELECT
    `receive`.`uid`,
    `receive`.`receive_no` AS `receiveNo`,
    `receive`.`receiver`,
    `receive`.`status`,
    `receive`.`phone`,
    `receive`.`use_datetime` AS `useDatetime`,
    `receive`.`real_usetime` AS `realUsetime`,
    `receive`.`created_at` AS `createdAt`,
    `receive`.`updated_at` AS `updatedAt`,
    `movements`.`uid` AS `movements.uid`,
    `movements`.`parking` AS `movements.parking`,
    `movements`.`parking_detail` AS `movements.parkingDetail`,
    `movements`.`section` AS `movements.section`,
    `movements`.`start_datetime` AS `movements.startDatetime`,
    `movements`.`end_datetime` AS `movements.endDatetime`,
    `movements`.`created_at` AS `movements.createdAt`
FROM
    `receives` AS `receive`
LEFT OUTER JOIN `movements` AS `movements` ON
    `receive`.`uid` = `movements`.`receive_uid`
    AND (`movements`.`deleted_at` IS NULL)
WHERE
    (`receive`.`deleted_at` IS NULL
        AND (`receive`.`real_usetime` BETWEEN ‘2023-05-26 00:00:00’ AND ‘2023-05-26 23:59:59’
            OR `receive`.`use_datetime` BETWEEN ‘2023-05-26 00:00:00’ AND ‘2023-05-26 23:59:59’)
        );

2-2. generated SQL of improvement code

SELECT
    `receive`.`uid`,
    `receive`.`receive_no` AS `receiveNo`,
    `receive`.`receiver`,
    `receive`.`status`,
    `receive`.`phone`,
    `receive`.`use_datetime` AS `useDatetime`,
    `receive`.`real_usetime` AS `realUsetime`,
    `receive`.`created_at` AS `createdAt`,
    `receive`.`updated_at` AS `updatedAt`,
    `movements`.`uid` AS `movements.uid`,
    `movements`.`parking` AS `movements.parking`,
    `movements`.`parking_detail` AS `movements.parkingDetail`,
    `movements`.`section` AS `movements.section`,
    `movements`.`start_datetime` AS `movements.startDatetime`,
    `movements`.`end_datetime` AS `movements.endDatetime`,
    `movements`.`created_at` AS `movements.createdAt`
FROM
    `receives` AS `receive`
LEFT OUTER JOIN `movements` AS `movements` ON
    `receive`.`uid` = `movements`.`receive_uid`
    AND (`movements`.`deleted_at` IS NULL)
WHERE
    (`receive`.`deleted_at` IS NULL
        AND ((`receive`.`real_usetime` >= ‘2023-05-26 00:00:00’
            AND `receive`.`real_usetime` < ‘2023-05-27 00:00:00’)
        OR (`receive`.`use_datetime` >= ‘2023-05-26 00:00:00’
            AND `receive`.`use_datetime` < ‘2023-05-27 00:00:00’)));

Answer 1

I don't think there's any significant difference between Op.between and Op.lte + Op.gte.

It seems plausible that you have improved the performance of the query by removing the include:/JOIN.

Other than that, I'm not really sure if there is an answer to what you have asked, given that I have understood you. If you want to improve database performance, you next step should probably be to look over the indexes. A index on the "realUsetime" column, if you currently done have one, could make a massive difference.