[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio commented on TEIID-4766:
-------------------------------------
Steven, I wrongly named this issue with_ INNER JOIN_ instead of *LEFT JOIN*. Sorry for the inconvenience. I've corrected the issue to reflect the LEFT JOIN naming.
> 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, 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 Inner Join in MySQL to boost performance.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4766:
--------------------------------
Attachment: server.log
> 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, 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 Inner Join in MySQL to boost performance.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio commented on TEIID-4766:
-------------------------------------
Sorry for not attaching the server.log before. I forgot to put the system in Debug and didn't see any useful information. I've now attached the server.log.
> 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, 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 Inner Join in MySQL to boost performance.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio commented on TEIID-4766:
-------------------------------------
Added missing attachments.
> 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)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
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)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4766:
---------------------------------------
> (please refer to MySqlQueryExplainPlan.png image)
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.
> So it is necessary to improve the way Teiid Server converts a Inner Join in MySQL to boost performance.
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:
> (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)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4766:
--------------------------------
Description:
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.
was:
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: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.
> 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:
> (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)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4766:
--------------------------------
Environment:
* MySql 5.6.35
* CentOs 7
* Teiid 9.1.2
* WildFly 10
was:
* MySql 5.6.35
* CentOs 7
* Teiid 9.1.2
> 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}
> 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)
9 years, 1 month
[JBoss JIRA] (TEIID-4766) Improve Inner Join performance queries when using MySQL 5 Translator
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4766:
--------------------------------
Description:
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: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.
was:
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: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.
> 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: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)
9 years, 1 month