Bug #100091 mysqlx interface is a lot slower than the connector.python interface
Submitted: 2 Jul 2020 12:23 Modified: 3 Jul 2020 8:13
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
Category:Connector / Python Severity:S5 (Performance)
Version:8.0.18, 8.0.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqlx, performance, XDevAPI

[2 Jul 2020 12:23] Daniël van Eeden
Using "mysqlx" (aka X DevAPI) with Connector/Python is a lot slower than the traditional mysql.connector interface.

This is what my testing shows.
mysqlx:          fetched   1000 rows in elapsed=0.18699979782104492
mysql.connector: fetched   1000 rows in elapsed=0.06336450576782227
mysqlx:          fetched   5000 rows in elapsed=0.8569087982177734
mysql.connector: fetched   5000 rows in elapsed=0.2925424575805664
mysqlx:          fetched  10000 rows in elapsed=1.7030038833618164
mysql.connector: fetched  10000 rows in elapsed=0.5785782337188721
mysqlx:          fetched 100000 rows in elapsed=16.321004152297974
mysql.connector: fetched 100000 rows in elapsed=5.475654363632202

How to repeat:
import mysqlx
import mysql.connector
import time

params = {'host': '', 'user': 'myuser', 'password': 'xxxxxxxx'}

for rows in [1000, 5000, 10000, 100000]:
    session = mysqlx.get_session(params)
    schema = session.get_schema('test')
    tasks = schema.get_table('big_table')
    start = time.time()
    elapsed = time.time() - start
    print(f"mysqlx:          fetched {rows:6d} rows in {elapsed=}")
    con = mysql.connector.connect(**params)
    cur = con.cursor()
    start = time.time()
    cur.execute(f"SELECT * FROM dbang.tasks_task LIMIT {rows}")
    [x for x in cur]
    elapsed = time.time() - start
    print(f"mysql.connector: fetched {rows:6d} rows in {elapsed=}")
[3 Jul 2020 8:13] MySQL Verification Team
Hello Daniël,

Thank you for the report and feedback.
Verified as described with Connector/Python 8.0.20.