[teiid-issues] [JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator

Pedro Inácio (JIRA) issues at jboss.org
Wed Feb 15 06:12:00 EST 2017


Pedro Inácio created TEIID-4766:
-----------------------------------

             Summary: Improve Inner Join performance queries when using MySQL 5 Translator
                 Key: TEIID-4766
                 URL: https://issues.jboss.org/browse/TEIID-4766
             Project: Teiid
          Issue Type: Enhancement
    Affects Versions: 9.1.2
         Environment: * MySql 5.6.35
* CentOs 7
* Teiid 9.1.2
            Reporter: Pedro Inácio
            Assignee: Steven Hawkins


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:


{code:sql}
CREATE VIEW vodafone_nl (
					mcc varchar(5),
					mnc varchar(5),
...
					INDEX (mcc,mnc)
				)
...
{code}

The numbering_plan table specification:

{code:sql}
CREATE TABLE numbering_plan (
mobile_country_code varchar(5),
					mobile_network_code varchar(5),
...
INDEX (mobile_country_code,mobile_network_code)
				)
...
{code}


The vodafone_nl_cache table specification:


{code:sql}
CREATE TABLE vodafone_nl (
					mcc varchar(5),
					mnc varchar(5),
...
					INDEX (mcc,mnc)
				)
...
{code}

The numbering_plan_cache table specification:

{code:sql}
CREATE TABLE numbering_plan_cache (
mobile_country_code varchar(5),
					mobile_network_code varchar(5),
...
INDEX (mobile_country_code,mobile_network_code)
				)
...
{code}


When executing the following query in a Client:

{code:sql}

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)
{code}

Teiid Server  will transform it in the following query:

{code:java}

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
{code}

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.


{code:sql}
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)
{code}


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 Inner Join in MySQL to boost performance.






--
This message was sent by Atlassian JIRA
(v7.2.3#72005)



More information about the teiid-issues mailing list