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

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.