mzhang77

Problem Summary: Suboptimal Plan for IN Subquery and Workarounds in TiDB

Background

The first SQL statement is the original query generated by the application.
Currently, the TiDB optimizer cannot automatically transform this form into the most efficient execution plan, resulting in a suboptimal plan.

A workaround was tested by rewriting the IN subquery into an EXISTS

mzhang77

-- 0) (Optional) Create a test database for isolation
CREATE DATABASE IF NOT EXISTS test;
USE test;

-- 1) Drop existing tables
DROP TABLE IF EXISTS edges;
DROP TABLE IF EXISTS nodes;

-- 2) Create nodes table

mzhang77

import mysql.connector
from mysql.connector import errorcode
import os

# Database configuration (adjust according to your local environment)
config = {
    'user': 'root',
    'password': os.getenv('MYSQL_PASSWORD'),
    'host': '127.0.0.1',
    'database': 'test',

mzhang77

mysql> show create table edges\G
*************************** 1. row ***************************
       Table: edges
Create Table: CREATE TABLE `edges` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `from_event_id` varchar(64) NOT NULL,
  `to_entity_id` varchar(64) NOT NULL,
  `to_entity_type` int NOT NULL,
  `from_event_type` int NOT NULL,

mzhang77

import mysql.connector
import random
import time

# ================= 配置区域 =================
DB_CONFIG = {
    'host': '127.0.0.1',  # 替换 TiDB IP
    'port': 4000,
    'user': 'root',       # 替换用户名
    'password': '',       # 替换密码

mzhang77

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int NOT NULL,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `padding` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,

mzhang77

import pymysql
import random
import time

# --- 数据库配置 ---
DB_CONFIG = {
    'host': '127.0.0.1',  # 默认为本地，如果 TiDB 在远程请修改 IP
    'port': 4000,
    'user': 'root',

mzhang77

mysql> show create table audit_log_events\G
*************************** 1. row ***************************
       Table: audit_log_events
Create Table: CREATE TABLE `audit_log_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_uuid` varbinary(16) NOT NULL,
  `created_at` timestamp(6) NOT NULL,
  `user_uuid` binary(16) DEFAULT NULL,
  `source_hash` binary(64) DEFAULT NULL,
  `device_code` varchar(32) DEFAULT NULL,

mzhang77

import pymysql
import random
import time
import binascii
from datetime import datetime, timedelta

# --- Database Configuration ---
DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 4000,

mzhang77

package main

import (
	"crypto/rand"
	"database/sql"
	"encoding/hex"
	"fmt"
	"log"
	"os"
	"strconv"