MySQL LEFT JOIN performance issue with external materialization management

For better understanding the performance problem, the description of the problem and possible enhancement will be done through a real example.

Having two tables defined in vdb:

• vodafone_nl
• numbering_plan

each having respectively: 1155 rows and 1,473,213 rows.

And also having each of these tables externally materialized in MySql in tables:

• vodafone_nl_cache
• numbering_plan_cache

The vodafone_nl table specification:

CREATE VIEW vodafone_nl (
        mcc varchar(5),
        mnc varchar(5),
...
	INDEX (mcc,mnc)
)
...

The numbering_plan table specification:

CREATE TABLE numbering_plan (
        mobile_country_code varchar(5),
	mobile_network_code varchar(5),
...
        INDEX (mobile_country_code,mobile_network_code)
)
...

The vodafone_nl_cache table specification:

CREATE TABLE vodafone_nl (
	mcc varchar(5),
	mnc varchar(5),
...
	INDEX (mcc,mnc)
)
...

The numbering_plan_cache table specification:

CREATE TABLE numbering_plan_cache (
        mobile_country_code varchar(5),
	mobile_network_code varchar(5),
...
        INDEX (mobile_country_code,mobile_network_code)
)
...

And having the translator defined as

	<translator name="mysql-override" type="mysql5">
		<property name="SupportsNativeQueries" value="true"/>
	</translator>

When executing the following query in a Client:

SELECT COUNT(*)
FROM
        VodafoneNl.vodafone_nl AS vnl
        LEFT JOIN NumberingPlan.numbering_plan AS np
        ON (np.mobile_country_code = vnl.mcc) AND (np.mobile_network_code = vnl.mnc)

Teiid Server will transform it in the following query:

SELECT COUNT(*) AS c_0 
FROM `mnom`.`vodafone_nl_cache` AS g_0  
            LEFT OUTER JOIN (
                    SELECT g_1.`mobile_country_code` AS c_0, g_1.`mobile_network_code` AS c_1 
                    FROM `mnom`.`numbering_plan_cache` AS g_1) AS v_0 
           ON v_0.c_0 = g_0.`mcc` AND v_0.c_1 = g_0.`mnc` 
LIMIT 200

This query will take 22 seconds in our system.

If we do an explain statement in MySqlWorkbench we observe the following:

(please refer to TeiidQueryExplainPlan.png image)

There are two Full Index Scans, one returning 1155 rows and a second returning 1452482 rows followed by a Non-Unique Key Lookup.

If the exact same query is run directly in MySql the system only takes 0.984 seconds to respond.

SELECT COUNT(*)
FROM
        vodafone_nl_cache AS vnl
        LEFT JOIN numbering_plan_cache AS np
        ON (np.mobile_country_code = vnl.mcc) AND (np.mobile_network_code = vnl.mnc)

If we do an explain statement in MySqlWorkbench we observe the following:

(please refer to MySqlQueryExplainPlan.png image)

There is one Full Index Scan, returning 1155 rows followed by a Non-Unique Key Lookup.

Between the two queries there is a difference of 21 seconds.

So it is necessary to improve the way Teiid Server converts a LEFT JOIN in MySQL to boost performance.