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;