Help Center/ GaussDB(for MySQL)/ Kernels/ Common Kernel Functions/ Conversion of IN Predicates Into Subqueries/ Performance Tests
Updated on 2024-09-06 GMT+08:00
View PDF
Share

Performance Tests

sysbench is used to perform a benchmark test.

  1. Prepare 10 million data records. 
    sysbench /usr/share/sysbench/oltp_read_only.lua --tables=1 --report-interval=10 --table-size=10000000  --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --time=300 --max-requests=0  --threads=200   prepare
  2. Run a statement where there are 10,000 elements in IN list. 
    select count(*) from sbtest1 where id/k in (... ...);

The following table lists the performance comparison.

Table 1 Performance data

Method

Function Enabled

Function Disabled (Not Suitable for range_opt)

Performance Comparison

Statements using indexes

0.09

2.48

Improved by 26.5 times