[teiid-issues] [JBoss JIRA] (TEIID-4766) MySQL LEFT JOIN performance issue with external materialization management

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13367062#comment-13367062 ] 

Steven Hawkins commented on TEIID-4766:
---------------------------------------

A full fix will require us to implement the status predicate check in a different way.  It will require more tracking through planning rather than the simple logic in relational planner, but it won't end up affecting the plan.  An alternative approach would be to attempt recreate the view removal logic in the rewriter rather than just having it in the planner.

> MySQL LEFT JOIN performance issue with external materialization management
> --------------------------------------------------------------------------
>
>                 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
>             Fix For: 9.3, 9.2.1
>
>         Attachments: MySqlQueryExplainPlan.png, TeiidQueryExplainPlan.png, server.log
>
>
> 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 LEFT JOIN in MySQL to boost performance.



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