VIDEX

English | 简体中文

VIDEX: The Disaggregated, Extensible [VI]rtual in[DEX] Engine for What-If Analysis in MySQL. - Virtual Index: Does not require real data, relies only on statistical information and algorithm models to accurately simulate MySQL query plans, table join orders, and index selections; - Disaggregated: VIDEX runs on a standalone instance without affecting production MySQL. Furthermore, the Statistic Server (optionally AI-enhanced to provide cardinality and ndv) can be deployed separately, enabling GPU-heterogeneous computing and seamless hot-updates. - Extensible: VIDEX offers convenient interfaces allowing users to apply models like cardinality and ndv to downstream MySQL tasks (e.g., index recommendation);

Latest News

• [2025-05-28] 🥳🎉 VIDEX demo paper has been accepted by the VLDB 2025 Demo Track! 🥳🎉 "VIDEX: A Disaggregated and Extensible Virtual Index for the Cloud and AI Era" (arXiv Preprint | How to Cite)
• [2025-04-28] VIDEX v0.1.0 is released.

What's VIDEX

The virtual index (aka hypothetical index) aims to simulate the cost of indexes within SQL query plans, thereby demonstrating to users the impact of indexes on SQL plans without the need to create actual indexes on raw instances. This technology is widely applied in various SQL optimization tasks, including index recommendation and table join order optimization. As a reference, many other databases already have virtual index features from official or third-party sources, such as Postgres, Oracle, and IBM DB2.

Note: The term virtual index used here is distinct from the "virtual index" referenced in the MySQL Official Documents, which refers to indexes built on virtual generated columns.

Additionally, VIDEX encapsulates a set of standardized interfaces for cost estimation, addressing popular topics in academic research such as cardinality estimation and NDV (Number of Distinct Values) estimation. Researchers and database developers can easily integrate custom algorithms into VIDEX for optimization tasks. By default, VIDEX includes implementations based on histograms and NDV collected from the ANALYZE TABLE or small-scale data sampling.

VIDEX offers two startup modes:

1. Plugin to production database (Plugin-Mode): Install VIDEX as a plugin to the production database instance.
2. Individual instance (Standalone-Mode): This mode can completely avoid impacting the stability of online running instances, making it practical for industrial environments.

Functionally, VIDEX supports creating and deleting indexes (single-column indexes, composite indexes, EXTENDED_KEYS indexes, descending indexes). However, it currently does not support functional indexes, FULL-Text, and Spatial Indexes.

In terms of accuracy, we have tested VIDEX on complex analytical benchmarks such as TPC-H, TPC-H-Skew, and JOB. Given only the oracle NDV and cardinality, the VIDEX query plan is 100% identical to InnoDB. (Refer to Example: TPC-H for additional details). We expect that VIDEX can provide users with a better platform to more easily test the effectiveness of cardinality and NDV algorithms, and apply them on SQL optimization tasks. VIDEX has been deployed in Bytedance production environment, serving large-scale slow SQL optimizations.

---

1. Architecture Overview

VIDEX consists of two parts:

• VIDEX-Optimizer-Plugin (abbr. VIDEX-Optimizer): Conducted a thorough review of over 90 interface functions in the MySQL handler, and implement the index-related parts.
• VIDEX-Statistic-Server (abbr. VIDEX-Statistic): The cost estimation service calculates NDV and Cardinality based on collected statistical information and estimation algorithms, and returns the results to the VIDEX-Optimizer instance.

VIDEX creates an individual virtual database according to the specified target database in the raw instance, containing a series of tables with the same DDL, but replacing the engine from InnoDB to VIDEX.

2 Quick Start

2.1 Install Python Environment

VIDEX requires Python 3.9 for metadata collection tasks. We recommend using Anaconda/Miniconda to create an isolated Python environment:

For Linux/macOS Users： ```bash

Clone repository

VIDEXHOME=videxserver git clone git@github.com:bytedance/videx.git $VIDEXHOME cd $VIDEXHOME

Create and activate Python environment

conda create -n videxpy39 python=3.9 conda activate videxpy39

Install VIDEX

python3.9 -m pip install -e . --use-pep517 ```

For Windows Users： ```cmd

Clone repository, Git must be pre-installed

set VIDEXHOME=videxserver git clone git@github.com:bytedance/videx.git %VIDEXHOME% cd %VIDEXHOME%

Create and activate Python environment

conda create -n videxpy39 python=3.9 conda activate videxpy39

Install VIDEX

python -m pip install -e . --use-pep517 ```

2.2 Launch VIDEX (Docker Mode)

For simplified deployment, we provide pre-built Docker images for both MySQL and MariaDB, containing:

MySQL Version: - VIDEX-Optimizer: Based on Percona-MySQL 8.0.34-26 with integrated VIDEX plugin - VIDEX-Server: NDV and cardinality algorithm service

MariaDB Version: - VIDEX-Optimizer: Based on MariaDB 11.8.2 with integrated VIDEX plugin - VIDEX-Server: NDV and cardinality algorithm service

Install Docker

If you haven't installed Docker yet: - Docker Desktop for Windows/Mac - Linux: Follow the official installation guide

Launch VIDEX Container

MySQL Version

bash docker run -d -p 13308:13308 -p 5001:5001 --name videx kangrongme/videx:latest

MariaDB Version

bash docker run -d -p 13308:13308 -p 5001:5001 --name mariadb_videx kangrongme/mariadb_videx:0.0.1

Port Information - 13308: MySQL/MariaDB service port - 5001: VIDEX-Server service port

Alternative Deployment Options

VIDEX also supports the following deployment methods, see Installation Guide: - Build complete MySQL Server from source - Build VIDEX plugin only and install on existing MySQL - Deploy VIDEX-Server independently (supports custom optimization algorithms)

3 Examples

3.1 TPCH-Tiny Example (MySQL 8.0)

This example demonstrates the complete VIDEX workflow using the TPC-H Tiny dataset (1% random sample from TPC-H sf1).

Environment Details

The example assumes all components are deployed locally via Docker:

Component | Connection Info ---|--- Target-MySQL (Production DB) | 127.0.0.1:13308, username:videx, password:password
VIDEX-Optimizer (Plugin) | Same as Target-MySQL VIDEX-Server | 127.0.0.1:5001

Step 1: Import Test Data

For Linux/macOS Users： ```bash cd $VIDEX_HOME

Create database (If you don't have the MySQL client installed on your machine, you need to download and install MySQL. After installation, do not start the MySQL server, as VIDEX will use the IP and port.)

mysql -h127.0.0.1 -P13308 -uvidex -ppassword -e "create database tpch_tiny;"

Import data

tar -zxf data/tpchtiny/tpchtiny.sql.tar.gz mysql -h127.0.0.1 -P13308 -uvidex -ppassword -Dtpchtiny < tpchtiny.sql ```

For Windows Uesrs： ```cmd

Change to project directory (assuming VIDEX_HOME environment variable is set)

cd %VIDEX_HOME%

Download test data

Create database

mysql -h127.0.0.1 -P13308 -uvidex -ppassword -e "create database tpch_tiny;"

Import data

tar -zxf data/tpchtiny/tpchtiny.sql.tar.gz mysql -h127.0.0.1 -P13308 -uvidex -ppassword -Dtpchtiny < tpchtiny.sql ```

Step 2: Collect and Import VIDEX Metadata

Ensure VIDEX environment is installed. If not, refer to 2.1 Install Python Environment.

For Linux/macOS Users: shell cd $VIDEX_HOME python src/sub_platforms/sql_opt/videx/scripts/videx_build_env.py \ --target 127.0.0.1:13308:tpch_tiny:videx:password \ --videx 127.0.0.1:13308:videx_tpch_tiny:videx:password

For Windows Users: ```cmd cd %VIDEX_HOME%

Windows CMD doesn't support \ as line continuation, parameters must be in the same line

python src/subplatforms/sqlopt/videx/scripts/videxbuildenv.py --target "127.0.0.1:13308:tpchtiny:videx:password" --videx "127.0.0.1:13308:vidextpch_tiny:videx:password" ```

Output: ```log 2025-02-17 13:46:48 [2855595:140670043553408] INFO root [videxbuildenv.py:178] - Build env finished. Your VIDEX server is 127.0.0.1:5001. You are running in non-task mode.

To use VIDEX, please set the following variable before explaining your SQL:

-- Connect VIDEX-Optimizer: mysql -h127.0.0.1 -P13308 -uvidex -ppassword -Dvidextpchtiny USE vidextpchtiny; SET @VIDEXSERVER='127.0.0.1:5001'; -- EXPLAIN YOURSQL; ```

Metadata is now collected and imported to VIDEX-Server. The JSON file is written to videx_metadata_tpch_tiny.json.

If users have prepared metadata files, they can specify --meta_path to skip collection and import directly.

Step 3: EXPLAIN SQL

Connect to VIDEX-Optimizer and execute EXPLAIN.

To demonstrate VIDEX's effectiveness, we compare EXPLAIN details for TPC-H Q21, a complex query with four-table joins involving WHERE, aggregation, ORDER BY, GROUP BY, EXISTS and self-joins. MySQL can choose from 11 indexes across 4 tables.

Since VIDEX-Server is deployed on the VIDEX-Optimizer node with default port (5001), we don't need to set VIDEX_SERVER additionally. If VIDEX-Server is deployed elsewhere, execute SET @VIDEX_SERVER first.

sql -- SET @VIDEX_SERVER='127.0.0.1:5001'; -- Not needed for Docker deployment -- Connect VIDEX-Optimizer: mysql -h127.0.0.1 -P13308 -uvidex -ppassword -Dvidex_tpch_tiny -- USE videx_tpch_tiny; EXPLAIN FORMAT = JSON SELECT s_name, count(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.l_suppkey AND o_orderkey = l1.l_orderkey AND o_orderstatus = 'F' AND l1.l_receiptdate > l1.l_commitdate AND EXISTS (SELECT * FROM lineitem l2 WHERE l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey) AND NOT EXISTS (SELECT * FROM lineitem l3 WHERE l3.l_orderkey = l1.l_orderkey AND l3.l_suppkey <> l1.l_suppkey AND l3.l_receiptdate > l3.l_commitdate) AND s_nationkey = n_nationkey AND n_name = 'IRAQ' GROUP BY s_name ORDER BY numwait DESC, s_name;

We compare VIDEX and InnoDB. We use EXPLAIN FORMAT=JSON, a more strict format. We compare not only table join order and index selection but every detail of query plans (e.g., rows and cost at each step).

As shown below, VIDEX (left) generates a query plan almost 100% identical to InnoDB (right). Complete EXPLAIN results are in data/tpch_tiny.

Note that VIDEX accuracy depends on three key algorithm interfaces: - ndv - cardinality - pct_cached (percentage of index data loaded in memory). Can be set to 0 (cold start) or 1 (hot data) if unknown, but production instances' pct_cached may vary constantly.

A key VIDEX function is simulating index costs. We add an extra index. VIDEX's index addition cost is O(1):

sql ALTER TABLE tpch_tiny.orders ADD INDEX idx_o_orderstatus (o_orderstatus); ALTER TABLE videx_tpch_tiny.orders ADD INDEX idx_o_orderstatus (o_orderstatus);

Re-running EXPLAIN shows MySQL-InnoDB and VIDEX query plans changed identically, both adopting the new index.

VIDEX's row estimate (7404) differs from MySQL-InnoDB (7362) by ~0.56%, due to cardinality estimation algorithm error.

Finally, we remove the index:

sql ALTER TABLE tpch_tiny.orders DROP INDEX idx_o_orderstatus; ALTER TABLE videx_tpch_tiny.orders DROP INDEX idx_o_orderstatus;

3.2 TPCH-Tiny Example (MySQL 5.7)

VIDEX now supports high-precision simulation for MySQL 5.7 in the standalone mode.

Step 1: Import Test Data into MySQL 5.7 Instance

Import data into a MySQL 5.7 instance.

bash mysql -h${HOST_MYSQL57} -P13308 -uvidex -ppassword -e "create database tpch_tiny_57;" mysql -h${HOST_MYSQL57} -P13308 -uvidex -ppassword -Dtpch_tiny_57 < tpch_tiny.sql

Step 2: VIDEX Collection and Import of VIDEX Metadata

VIDEX employs a different data collection method for MySQL 5.7 compared to MySQL 8.0, while maintaining the same command parameters.

bash cd $VIDEX_HOME python src/sub_platforms/sql_opt/videx/scripts/videx_build_env.py \ --target ${HOST_MYSQL57}:13308:tpch_tiny_57:videx:password \ --videx 127.0.0.1:13308:videx_tpch_tiny_57:videx:password

Step 2.5: ✴️ Setting Parameters Adapted for MySQL 5.7

VIDEX can simulate MySQL 5.7 in standalone mode. Due to the differences between MySQL 5.7 and MySQL 8.0, we need to set the optimizer-switch variables and server_cost tables for VIDEX-optimizer.

✴️✴️ Note that, Since setting the environment does not take effect in the current connection, please run the following script first, then log into MySQL.

bash mysql -h ${HOST_MYSQL57} -P13308 -uvidex -ppassword < src/sub_platforms/sql_opt/videx/scripts/setup_mysql57_env.sql

Step 3: EXPLAIN SQL

We will use TPC-H Q21 as an example. The EXPLAIN result is as follows. We can see that the query plan for MySQL 5.7 differs significantly from MySQL 8.0, yet VIDEX can still simulate it accurately:

Below is a comparison of EXPLAIN cost details between MySQL 5.7 and VIDEX.

Step 4: ✴️ Clear MySQL 5.7 Environment Variables

If you wish to revert the MySQL-optimizer to MySQL 8.0, please run the following script.

bash mysql -h ${HOST_MYSQL57} -P13308 -uvidex -ppassword < src/sub_platforms/sql_opt/videx/scripts/clear_mysql57_env.sql

3.3 TPCH-Tiny Example (MariaDB 11.8)

VIDEX supports high-precision simulation of MariaDB 11.8.

Environment Setup

Environment configuration is the same as 3.1 MySQL 8.0 Example.

Step 1: Import Test Data

Refer to 3.1 MySQL 8.0 Example Step 1.

Step 2: VIDEX Collection and Import of VIDEX Metadata

Refer to 3.1 MySQL 8.0 Example Step 2.

Step 3: EXPLAIN SQL

When performing InnoDB comparison in MariaDB environment, it's recommended to execute the following command:

sql SET SESSION use_stat_tables=NEVER;

Generating histograms will modify system tables such as mysql.column_stats, which can affect optimizer behavior. This command ensures the optimization process only relies on InnoDB persistent statistics stored in mysql.innodb_table_stats and mysql.innodb_index_stats.

Using TPC-H Q21 as an example, the EXPLAIN results are shown in the figure below. VIDEX maintains high-precision simulation, with row count differences mainly stemming from histogram sampling data.

Execute the same index creation operation: sql ALTER TABLE tpch_tiny.orders ADD INDEX idx_o_orderstatus (o_orderstatus); ALTER TABLE videx_tpch_tiny.orders ADD INDEX idx_o_orderstatus (o_orderstatus);

Re-running EXPLAIN shows that both MariaDB-InnoDB and VIDEX query plans changed identically, both adopting the new index.

Finally, we remove the index: sql ALTER TABLE tpch_tiny.orders DROP INDEX idx_o_orderstatus; ALTER TABLE videx_tpch_tiny.orders DROP INDEX idx_o_orderstatus;

3.3 TPCH sf1 (1g) Example (MySQL 8.0)

We provide metadata file for TPC-H sf1: data/videx_metadata_tpch_sf1.json, allowing direct import without collection.

For Linux/macOS Users: ```shell cd $VIDEXHOME python src/subplatforms/sqlopt/videx/scripts/videxbuildenv.py \ --target 127.0.0.1:13308:tpchsf1:user:password \ --metapath data/tpchsf1/videxmetadatatpch_sf1.json

```

For Windows Users: cmd cd %VIDEX_HOME% python src/sub_platforms/sql_opt/videx/scripts/videx_build_env.py --target 127.0.0.1:13308:tpch_sf1:user:password --meta_path data/tpch_sf1/videx_metadata_tpch_sf1.json

Like TPCH-tiny, VIDEX generates nearly identical query plans to InnoDB for TPCH-sf1 Q21, see data/tpch_sf1.

4. API

Specify connection methods for original database and videx-stats-server. Collect statistics from original database, save to intermediate file, then import to VIDEX database.

• If VIDEX-Optimizer starts separately rather than installing plugin on target-MySQL, users can specify VIDEX-Optimizer address via --videx
• If VIDEX-Server starts separately rather than deploying on VIDEX-Optimizer machine, users can specify VIDEX-Server address via --videx_server
• If users have generated metadata file, specify --meta_path to skip collection

Command example:

bash cd $VIDEX_HOME/src/sub_platforms/sql_opt/videx/scripts python videx_build_env.py --target 127.0.0.1:13308:tpch_tiny:videx:password \ [--videx 127.0.0.1:13309:videx_tpch_tiny:videx:password] \ [--videx_server 127.0.0.1:5001] \ [--meta_path /path/to/file]

5. 🚀Integrate Your Custom Model🚀

Method 1: Add into VIDEX-Statistic-Server

Users can fully implement VidexModelBase.

If users focus on cardinality and ndv (two popular research topics), they can also inherit from VidexModelInnoDB (see VidexModelExample). VidexModelInnoDB abstracts away complexities such as system variables and index metadata formats, providing a basic (heuristic) algorithm for ndv and cardinality.

```python class VidexModelBase(ABC): """ Abstract cost model class. VIDEX-Statistic-Server receives requests from VIDEX-Optimizer for Cardinality and NDV estimates, parses them into structured data for ease use of developers.

Implement these methods to inject Cardinality and NDV algorithms into MySQL.
"""

@abstractmethod
def cardinality(self, idx_range_cond: IndexRangeCond) -> int:
    """
    Estimates the cardinality (number of rows matching a criteria) for a given index range condition.

    Parameters:
        idx_range_cond (IndexRangeCond): Condition object representing the index range.

    Returns:
        int: Estimated number of rows that match the condition.

    Example:
        where c1 = 3 and c2 < 3 and c2 > 1, ranges = [RangeCond(c1 = 3), RangeCond(c2 < 3 and c2 > 1)]
    """
    pass

@abstractmethod
def ndv(self, index_name: str, table_name: str, column_list: List[str]) -> int:
    """
    Estimates the number of distinct values (NDV) for specified fields within an index.

    Parameters:
        index_name (str): Name of the index.
        table_name (str): Table Name
        column_list (List[str]): List of columns(aka. fields) for which NDV is to be estimated.

    Returns:
        int: Estimated number of distinct values.

    Example:
        index_name = 'idx_videx_c1c2', table_name= 't1', field_list = ['c1', 'c2']
    """
    raise NotImplementedError()

```

Method 2: Implement a New VIDEX-Statistic-Server

VIDEX-Optimizer will request NDV and cardinality results via HTTP based on the user-specified address. Therefore, users can implement the HTTP response in any programming language.

License

This project is dual-licensed:

• The MySQL engine implementation is licensed under GPL-2.0
• All other codes and scripts are licensed under MIT

See the LICENSE directory for details.

Paper Citation

If you find this code useful, we would appreciate citations to our paper:

@misc{kang2025videx, title={VIDEX: A Disaggregated and Extensible Virtual Index for the Cloud and AI Era}, author={Rong Kang and Shuai Wang and Tieying Zhang and Xianghong Xu and Linhui Xu and Zhimin Liang and Lei Zhang and Rui Shi and Jianjun Chen}, year={2025}, eprint={2503.23776}, archivePrefix={arXiv}, primaryClass={cs.DB}, url={https://arxiv.org/abs/2503.23776}, }

Version Support

Plugin-Mode Support List

| Database System | Version Range | Support Status | Remarks | |-----------------|---------------|----------------|------------------------------------------------| | Percona | 8.0.34-26 | ✅ Supported | Tested in all TPC-H and JOB scenarios | | MySQL | 8.0.42 | ✅ Supported | Branch compatibility/mysql8.0.42 | | MariaDB | — | ⏳ Planning | Ongoing discussions with the MariaDB community | | PG | - | 🔮 Future Work | Anticipating discussions with contributors |

Standalone-Mode Support List

| Database System | Version Range | Support Status | Remarks | |-----------------|---------------|------------------|------------------------------------------------| | Percona | 8.0.34-26+ | ✅ Supported | Tested in all TPC-H and JOB scenarios | | MySQL | 8.0.x | ✅ Supported | Tested in some TPC-H scenarios | | MySQL | 5.7.x | ✅ Supported | Tested in some TPC-H scenarios | | MariaDB | 11.8.2 | ✅ Supported | Tested in some TPC-H scenarios | | PG | - | 🔮 Future Work | Anticipating discussions with contributors |

Authors

ByteBrain Team, Bytedance

Contact

If you have any questions, feel free to contact us:

• Rong Kang: kangrong.cn@bytedance.com, kr11thss@gmail.com,
• Tieying Zhang: tieying.zhang@bytedance.com