MySQL Forums
Forum List  »  Quality Assurance

FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true`
Posted by: chen yi
Date: January 20, 2022 10:59PM

Details:

mysql version: Server version: 8.0.25 MySQL Community Server - GPL
jdbc connector version: mysql-connector-java-8.0.25

reproduce:
step 1: create table:


CREATE DATABASE PRICING;
USE PRICING:
CREATE TABLE `QUOTE` (
`ID` BIGINT NOT NULL,
`GROUP_ID` SMALLINT NOT NULL,
`SYMBOL_ID` SMALLINT NOT NULL,
`SOURCE_ID` BIGINT NULL DEFAULT NULL,
`PRODUCT_ID` INT NOT NULL,
`COMPANY_ID` TINYINT NOT NULL,
`LOW` DECIMAL(15,6) NOT NULL,
`HIGH` DECIMAL(15,6) NOT NULL,
`OPEN` DECIMAL(15,6) NOT NULL,
`SCALE` TINYINT NOT NULL,
`STATUS` TINYINT NOT NULL,
`LADDERED` TINYINT NOT NULL,
`DEVIATION` DECIMAL(15,6) NOT NULL,
`BID_PRICE` DECIMAL(15,6) NOT NULL,
`ASK_PRICE` DECIMAL(15,6) NOT NULL,
`QUOTE_DATE` DATE NOT NULL,
`QUOTE_TIME` BIGINT NOT NULL,
`BID_TRADABLE` TINYINT NOT NULL,
`ASK_TRADABLE` TINYINT NOT NULL,
`BID_ADJUSTMENT` DECIMAL(15,6) NOT NULL,
`ASK_ADJUSTMENT` DECIMAL(15,6) NOT NULL,
`VERSION` INT NOT NULL DEFAULT '1',
`INSERT_DATETIME` TIMESTAMP NULL DEFAULT NULL,
`UPDATE_DATETIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
INDEX `INDEX_01` (`SOURCE_ID`),
INDEX `INDEX_02` (`PRODUCT_ID`),
INDEX `INDEX_03` (`PRODUCT_ID`, `QUOTE_DATE`),
INDEX `INDEX_04` (`PRODUCT_ID`, `QUOTE_TIME`)
)
ENGINE=InnoDB;


step 2: insert 10,000,000 records to above table. `PRODUCT_ID` 's cardinality is 200.

step 3: use following java code to access above table

public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/PRICING?useServerPrepStmts=true", "user", "pass")) {
try (PreparedStatement ps = conn.prepareStatement("select * from QUOTE FORCE INDEX(INDEX_02) where PRODUCT_ID = ? order by ID desc limit 512")) {
ps.setInt(1, 10);
try (ResultSet rs = ps.executeQuery()) {
int count = 0;
while (rs.next()) {
count++;
}
System.out.println("count:" + count);
}
}
}
}

we expect above sql to use INDEX_02, key = INDEX_02, Extra = Backward index scan

above code takes very long time to fetch result. if we changed `useServerPrepStmts=true` to `false` or use `Statement` instead of `PreparedStatement` or replace `FORCE INDEX` to `FORCE INDEX FOR JOIN`. it's fast as normal.

mysql execution Plan:

mysql> EXPLAIN select * from QUOTE FORCE INDEX(INDEX_02) where PRODUCT_ID = 1 order by ID desc limit 512;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
| 1 | SIMPLE | QUOTE | NULL | ref | INDEX_02 | INDEX_02 | 4 | const | 1 | 100.00 | Backward index scan |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true`
225
January 20, 2022 10:59PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.