# How to get a performance upgrade to this complicated Select...

**URL:** <https://forums.percona.com/t/how-to-get-a-performance-upgrade-to-this-complicated-select/4294>\
**Category:** Other MySQL® Questions\
**Created:** [July 9, 2015, 9:50am UTC](https://forums.percona.com/t/how-to-get-a-performance-upgrade-to-this-complicated-select/4294 "2015-07-09T09:50:59Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ZeroG25](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@ZeroG25](https://forums.percona.com/u/ZeroG25)\
**Post date:** [July 9, 2015, 9:50am UTC](https://forums.percona.com/t/how-to-get-a-performance-upgrade-to-this-complicated-select/4294/1 "2015-07-09T09:50:59Z")

</div>

Hey  
First of all, I am really new here in this Forum and so, the first I have to say is: HELLO  
Ok - I have the following Problem with a Select.  
I create temporary tables, because I have to concat some data. - The second query, has 4 UNION ALL Statements.  
I can’t Change the database tables. - All I can do is to perform the Select statments and/or create Indexes.  
I do have parallel a MS SQL Server (not in production - the mySQL Server is the production Server) - There the statment runs all in all within half a second. - On the mySQL Server the same Statement Needs about 4 seconds… (same Hardware)  
I hope somebody can help me with Tuning of this (Please don’t doubt on the Statement, it is put togegther from my Software, where, like in this case, some values are the same. - so it Looks like there is the same Statement over and over, but this depends on the customers Needs!)  
So, here is the Statement

DROP TEMPORARY TABLE IF EXISTS tmpGlaeser;  
DROP TEMPORARY TABLE IF EXISTS tmpSchicht;

CREATE TEMPORARY TABLE tmpGlaeser (  
PRIMARY KEY (ID),  
INDEX (hst\_code\_grundglas),  
INDEX (lieferbar\_ab),  
INDEX (lieferbar\_bis)  
) ENGINE = MEMORY  
SELECT DISTINCT  
iprolenstype.\*  
FROM iprolensrange iprolensrange  
INNER JOIN iprolenstype iprolenstype  
ON iprolensrange.hst\_code\_grundglas = iprolenstype.hst\_code\_grundglas  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100)  
AND iprolenstype.hst\_code\_grundglas IN (SELECT  
hst\_code\_grundglas  
FROM iprolensrange  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100))  
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar\_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar\_bis, CURDATE()))  
AND (0 \<= iprolensrange.prisma\_bis)  
AND (0 \<= iprolensrange.prisma\_bis  
);  
CREATE TEMPORARY TABLE tmpSchicht(  
INDEX (grundglas),  
INDEX (photo),  
INDEX (schicht))  
ENGINE = MEMORY  
SELECT  
grundglas,  
photo,  
schicht  
FROM (SELECT DISTINCT  
i1.hst\_code\_grundglas AS grundglas,  
iprooptions.Phototrop AS photo,  
iprooptions.hst\_code\_schicht AS schicht  
FROM iprooptions  
LEFT JOIN iprocombination i1  
ON (iprooptions.hst\_code\_schicht = i1.hst\_code\_schicht1  
AND i1.hst\_code\_grundglas IN (SELECT DISTINCT  
iprolenstype.hst\_code\_grundglas  
FROM iprolensrange iprolensrange  
INNER JOIN iprolenstype iprolenstype  
ON iprolensrange.hst\_code\_grundglas = iprolenstype.hst\_code\_grundglas  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100)  
AND iprolenstype.hst\_code\_grundglas IN (SELECT  
hst\_code\_grundglas  
FROM iprolensrange  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100))  
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar\_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar\_bis, CURDATE()))  
AND (0 \<= iprolensrange.prisma\_bis)  
AND (0 \<= iprolensrange.prisma\_bis))  
AND i1.hst\_code\_schicht1 \<\> ‘**’  
AND i1.lieferbarkeit = 2  
AND iprooptions.manufacturer\_code = i1.manufacturer\_code)  
WHERE iprooptions.Farbe \> 0  
UNION ALL  
SELECT DISTINCT  
i2.hst\_code\_grundglas AS grundglas,  
iprooptions.Phototrop AS photo,  
iprooptions.hst\_code\_schicht AS schicht  
FROM iprooptions  
LEFT JOIN iprocombination i2  
ON (iprooptions.hst\_code\_schicht = i2.hst\_code\_schicht2  
AND i2.hst\_code\_grundglas IN (SELECT DISTINCT  
iprolenstype.hst\_code\_grundglas  
FROM iprolensrange iprolensrange  
INNER JOIN iprolenstype iprolenstype  
ON iprolensrange.hst\_code\_grundglas = iprolenstype.hst\_code\_grundglas  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100)  
AND iprolenstype.hst\_code\_grundglas IN (SELECT  
hst\_code\_grundglas  
FROM iprolensrange  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100))  
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar\_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar\_bis, CURDATE()))  
AND (0 \<= iprolensrange.prisma\_bis)  
AND (0 \<= iprolensrange.prisma\_bis))  
AND i2.hst\_code\_schicht1 \<\> '**’  
AND i2.lieferbarkeit = 2  
AND iprooptions.manufacturer\_code = i2.manufacturer\_code)  
WHERE iprooptions.Farbe \> 0  
UNION ALL  
SELECT DISTINCT  
i3.hst\_code\_grundglas AS grundglas,  
iprooptions.Phototrop AS photo,  
iprooptions.hst\_code\_schicht AS schicht  
FROM iprooptions  
LEFT JOIN iprocombination i3  
ON (iprooptions.hst\_code\_schicht = i3.hst\_code\_schicht1  
AND i3.hst\_code\_grundglas IN (SELECT DISTINCT  
iprolenstype.hst\_code\_grundglas  
FROM iprolensrange iprolensrange  
INNER JOIN iprolenstype iprolenstype  
ON iprolensrange.hst\_code\_grundglas = iprolenstype.hst\_code\_grundglas  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100)  
AND iprolenstype.hst\_code\_grundglas IN (SELECT  
hst\_code\_grundglas  
FROM iprolensrange  
WHERE (1.5 BETWEEN iprolensrange.shs\_von / 100 AND iprolensrange.shs\_bis / 100)  
AND (0 BETWEEN iprolensrange.cyl\_von / 100 AND iprolensrange.cyl\_bis / 100))  
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar\_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar\_bis, CURDATE()))  
AND (0 \<= iprolensrange.prisma\_bis)  
AND (0 \<= iprolensrange.prisma\_bis))  
AND i3.hst\_code\_schicht1 \<\> ‘\*\*\*\*\*\*’  
AND i3.lieferbarkeit = 2  
AND iprooptions.manufacturer\_code = i3.manufacturer\_code)  
WHERE iprooptions.Farbe \> 0) AS newTable  
WHERE grundglas IS NOT NULL;  
UPDATE tmpGlaeser g, tmpSchicht s  
SET g.Phototrop = s.photo  
WHERE g.hst\_code\_grundglas = s.grundglas  
AND g.Phototrop \< s.photo;  
SELECT  
\*  
FROM tmpGlaeser ORDER BY manufacturer\_code, hst\_code\_grundglas;