[JBoss JIRA] (TEIID-4766) Improve LEFT 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 edited comment on TEIID-4766 at 2/15/17 9:12 AM:
--------------------------------------------------------------
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.
was (Author: pringi):
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 LEFT 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 LEFT 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 LEFT 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 edited comment on TEIID-4766 at 2/15/17 9:11 AM:
--------------------------------------------------------------
Added missing attachments.
was (Author: pringi):
[Regarding this comment|https://issues.jboss.org/browse/TEIID-4766?focusedCommentId=13363...].
Added missing attachments.
> Improve LEFT 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 LEFT 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 LEFT 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:
---------------------------------------
The full issue is that using external materialization management leaves a subquery in place as a guard that checks the status of the materialized table. That then prevents the removal of the inline view. With just internal materialization, a regular view or inline view, etc. the pushdown query would be as you would expect. This is not particular to mysql other than their optimizer is not recognizing that the inline view can be removed and is creating a suboptimal plan from there.
> Improve LEFT 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 LEFT 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 LEFT 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 edited comment on TEIID-4766 at 2/15/17 9:11 AM:
--------------------------------------------------------------
[Regarding this comment|https://issues.jboss.org/browse/TEIID-4766?focusedCommentId=13363... missing attachments.
was (Author: pringi):
Added missing attachments.
> Improve LEFT 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 LEFT 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 LEFT 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 edited comment on TEIID-4766 at 2/15/17 9:11 AM:
--------------------------------------------------------------
[Regarding this comment|https://issues.jboss.org/browse/TEIID-4766?focusedCommentId=13363...].
Added missing attachments.
was (Author: pringi):
[Regarding this comment|https://issues.jboss.org/browse/TEIID-4766?focusedCommentId=13363... missing attachments.
> Improve LEFT 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 LEFT 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 LEFT 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 LEFT 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}
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.
> Improve LEFT 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 LEFT 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 LEFT 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:
--------------------------------
Summary: Improve LEFT Join performance queries when using MySQL 5 Translator (was: Improve Inner Join performance queries when using MySQL 5 Translator)
> Improve LEFT 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 LEFT 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:
--------------------------------
Summary: Improve LEFT JOIN performance queries when using MySQL 5 Translator (was: Improve LEFT Join performance queries when using MySQL 5 Translator)
> Improve LEFT 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