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

[Pedro Inácio (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pedro Inácio updated TEIID-4766:
--------------------------------
    Attachment: MySqlQueryExplainPlan.png
                TeiidQueryExplainPlan.png


> 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
> * WildFly 10
>            Reporter: Pedro Inácio
>            Assignee: Steven Hawkins
>         Attachments: MySqlQueryExplainPlan.png, TeiidQueryExplainPlan.png
>
>
> 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}
> And having the translator defined as 
> {code:xml}
> 	<translator name="mysql-override" type="mysql5">
> 		<property name="SupportsNativeQueries" value="true"/>
> 	</translator>
> {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:sql}
> 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)