5:22 a.m.
[
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)