performance impact of bigint vs varchar in mysql query-CodePudding

Mysql giving bad performance while passing column value as bigint and column created with data type is varchar. Below is my create table query and index created on 3 columns(user_id,category,status). I am using spring jpa to interact with mysql and also using native query to get data from mysql.

CREATE TABLE order (
    id          int NOT NULL AUTO_INCREMENT,
    user_id     varchar(50) NOT NULL,
    category    varchar(255) NOT NULL,
    amount      decimal(13,2) NOT NULL,
    status      varchar(50) NOT NULL,
    created_at  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_order_user_category_status (user_id,category,status),
    KEY idx_order_created_at (created_at)
);

@Query(
    value =
        "SELECT month(created_at) mm, year(created_at) yy, sum(amount) tpv "
              "FROM order "
              "WHERE user_id = :userId "
              "AND category = 'BILL' "
              "AND status IN ('SUCCESS','PENDING') "
              "AND created_at >= :startDate AND created_at < :endDate "
              "GROUP BY mm, yy",
    nativeQuery = true)
List<List<Object>> getTpvByUserIdAndDateBetween(
    @Param("userId") Long userId,
    @Param("startDate") LocalDate startDate,
    @Param("endDate") LocalDate endDate);

SELECT month(created_at) mm, year(created_at) yy, sum(amount) tpv FROM order WHERE user_id = ? AND category = 'BILL' AND status IN ('SUCCESS', 'PENDING') AND created_at >= ? AND created_at < ? GROUP BY mm, yy
TRACE o.h.type.descriptor.sql.BasicBinder.bind(64) - binding parameter [1] as [BIGINT] - [24463608]
TRACE o.h.type.descriptor.sql.BasicBinder.bind(64) - binding parameter [2] as [DATE] - [2022-07-31]
TRACE o.h.type.descriptor.sql.BasicBinder.bind(64) - binding parameter [3] as [DATE] - [2022-10-01]
INFO  c.b.b.p.services.impl.OrderService.getCcBpMonthlyTpv(672) - db query response time:2398


@Query(
    value =
        "SELECT month(created_at) mm, year(created_at) yy, sum(amount) tpv "
              "FROM order "
              "WHERE user_id = :userId "
              "AND category = 'BILL' "
              "AND status IN ('SUCCESS', 'PENDING') "
              "AND created_at >= :startDate AND created_at < :endDate "
              "GROUP BY mm, yy",
    nativeQuery = true)
List<List<Object>> getCcBpTpvByIdAndDateBetween(
    @Param("userId") String userId,
    @Param("startDate") LocalDate startDate,
    @Param("endDate") LocalDate endDate);

SELECT month(created_at) mm, year(created_at) yy, sum(amount) tpv FROM order WHERE user_id = ? AND category = 'BILL' AND status IN ('SUCCESS', 'PENDING') AND created_at >= ? AND created_at < ? GROUP BY mm, yy
TRACE o.h.type.descriptor.sql.BasicBinder.bind(64) - binding parameter [1] as [VARCHAR] - [24463608]
TRACE o.h.type.descriptor.sql.BasicBinder.bind(64) - binding parameter [2] as [DATE] - [2022-07-31]
TRACE o.h.type.descriptor.sql.BasicBinder.bind(64) - binding parameter [3] as [DATE] - [2022-10-01]
INFO  c.b.b.p.services.impl.OrderService.getCcBpMonthlyTpv(686) - db query response time:8

Below are the cases after my analysis:

passed user_id as long and long was interpreted by jpa as bigint and got response in 2398ms.
passed user_id as string which was interpreted by jpa as varchar and got response in 8ms.

Tech stack:

JDK: 11
Spring boot version: 2.5.5
mysql version: 8.0.23

Note:

Approx 3 Million records present in table

Questions

How does mysql behave if column value is passed as different data type while doing a select?
Both gave correct result but one gave worst performance and other scenario gave best performance. Why?

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html says:

In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

So in this comparison term:

WHERE user_id = :userId

If you pass userId as a numeric parameter, then the query must convert string values in the user_id column to their numeric equivalent row by row. It can't assume the numeric value before conversion. So it can't know if the numeric value will end up in the same place in the index, and therefore the index is useless for that comparison.

CodePudding user response：

You did not include the EXPLAIN output for the queries, but most likely passing the wrong datatype causes implicit datatype conversion and mySQL not using the index.