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

[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4766: --------------------------------------- I don't see anything attached. The most important aspect from what you are showing above is why the inline view for inner side of the outer join is not removed. I however cannot reproduce this behavior based upon the description. Please provide the Teiid query plan debug log. Just to make sure I'm not missing anything, I don't see an inner join present in this issue. Is there something more general or different that you are trying to highlight? > 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 > > 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: > 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. -- This message was sent by Atlassian JIRA (v7.2.3#72005)