StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Main problem is probably indexes, but also well worth removing the co related sub query. Something like this:-</p> <pre><code>CREATE OR REPLACE VIEW view_test AS SELECT nve_nr.nve_nvenr AS collonr, CONCAT (adr_emp.adr_name1, "\n", adr_emp.adr_name2, "\n", adr_emp.adr_strasse, "\n", adr_emp.adr_land , " - ", adr_emp.adr_plz, " ", adr_emp.adr_ort) AS emp_adr, UNIX_TIMESTAMP((SELECT MAX(t_1.colst_timestamp) FROM tbl_collo_status t_1 WHERE t_1.colst_nve_id = nve_nr.nve_id AND t_1.colst_status_code IN (10, 11, 76))) AS status_x, UNIX_TIMESTAMP((SELECT MAX(t_2.colst_timestamp) FROM tbl_collo_status t_2 WHERE t_2.colst_nve_id = nve_nr.nve_id AND t_2.colst_status_code IN (20,25,91))) AS status_y, UNIX_TIMESTAMP((SELECT MAX(t_3.colst_timestamp) FROM tbl_collo_status t_3 WHERE t_3.colst_nve_id = nve_nr.nve_id AND t_3.colst_status_code IN (30,99,104))) AS status_z, TIME_TO_SEC(TIMEDIFF((SELECT MAX(t_2.colst_timestamp) FROM tbl_collo_status t_2 WHERE t_2.colst_nve_id = nve_nr.nve_id AND t_2.colst_status_code IN (20,25,91)), (SELECT MAX(t_1.colst_timestamp) FROM tbl_collo_status t_1 WHERE t_1.colst_nve_id = nve_nr.nve_id AND t_1.colst_status_code IN (10, 11, 76)))) AS timediff_xy, TIME_TO_SEC(TIMEDIFF((SELECT MAX(t_3.colst_timestamp) FROM tbl_collo_status t_3 WHERE t_3.colst_nve_id = nve_nr.nve_id AND t_3.colst_status_code IN (30,99,104)), (SELECT MAX(t_2.colst_timestamp) FROM tbl_collo_status t_2 WHERE t_2.colst_nve_id = nve_nr.nve_id AND t_2.colst_status_code IN (20,25,91)))) AS timediff_yz, TIME_TO_SEC(TIMEDIFF((SELECT MAX(t_3.colst_timestamp) FROM tbl_collo_status t_3 WHERE t_3.colst_nve_id = nve_nr.nve_id AND t_3.colst_status_code IN (30,99,104)), (SELECT MAX(t_1.colst_timestamp) FROM tbl_collo_status t_1 WHERE t_1.colst_nve_id = nve_nr.nve_id AND t_1.colst_status_code IN (10, 11, 76)))) AS timediff_xz, CAST(SUBSTR(nve_nr.nve_nvenr, 1, 3) AS UNSIGNED INTEGER) AS filter_collonr, MAX(t_5.colst_status_code) AS filter_last_status, sdg.sdg_kunde AS filter_kunden_id, sdg.sdg_id AS filter_sdg_id, nve_nr.nve_id AS filter_nve_id, adr_emp.adr_id AS filter_adr_emp_id FROM tbl_nve_nr nve_nr LEFT JOIN tbl_sendungen sdg ON nve_nr.nve_sdg_id = sdg.sdg_id LEFT JOIN tbl_adressen adr_emp ON adr_emp.adr_id = sdg.sdg_adr2_id INNER JOIN tbl_collo_status t_5 ON t_5.colst_nve_id = nve_nr.nve_id GROUP BY collonr, emp_adr, status_x, status_y, status_z, timediff_xy, timediff_yz, timediff_xz, filter_collonr, filter_kunden_id, filter_sdg_id, filter_nve_id, filter_adr_emp_id </code></pre> <p>Problem is that your query has a LOT of co related sub queries and these generally perform badly.</p> <p>What may be the best solution is to replace all the co related sub queries with joins against other views (with those views containing the selects)</p> <p>EDIT - splitting off the other subselects :-</p> <p>Create the views:-</p> <pre><code>CREATE ALGORITHM = UNDEFINED VIEW vw_MaxTimeStamp_10_11_76 AS SELECT t_1.colst_nve_id, MAX(t_1.colst_timestamp) AS MaxTimestamp FROM tbl_collo_status t_1 WHERE t_1.colst_status_code IN (10, 11, 76) GROUP BY t_1.colst_nve_id CREATE ALGORITHM = UNDEFINED VIEW vw_MaxTimeStamp_20_25_91 AS SELECT t_1.colst_nve_id, MAX(t_1.colst_timestamp) AS MaxTimestamp FROM tbl_collo_status t_1 WHERE t_1.colst_status_code IN (20,25,91) GROUP BY t_1.colst_nve_id CREATE ALGORITHM = UNDEFINED VIEW vw_MaxTimeStamp_30_99_104 AS SELECT t_1.colst_nve_id, MAX(t_1.colst_timestamp) AS MaxTimestamp FROM tbl_collo_status t_1 WHERE t_1.colst_status_code IN (30,99,104) GROUP BY t_1.colst_nve_id CREATE ALGORITHM = UNDEFINED VIEW vw_MaxStatusCode AS SELECT colst_nve_id, MAX(t_5.colst_status_code) AS MaxStatusCode FROM tbl_collo_status t_5 GROUP BY colst_nve_id </code></pre> <p>Then your main view can be simplified to something like this (afraid not tested so there may be a few typos)</p> <pre><code>CREATE OR REPLACE VIEW view_test AS SELECT nve_nr.nve_nvenr AS collonr, CONCAT (adr_emp.adr_name1, "\n", adr_emp.adr_name2, "\n", adr_emp.adr_strasse, "\n", adr_emp.adr_land , " - ", adr_emp.adr_plz, " ", adr_emp.adr_ort) AS emp_adr, UNIX_TIMESTAMP(vw_MaxTimeStamp_10_11_76.MaxTimestamp) AS status_x, UNIX_TIMESTAMP(vw_MaxTimeStamp_20_25_91.MaxTimestamp) AS status_y, UNIX_TIMESTAMP(vw_MaxTimeStamp_30_99_104.MaxTimestamp) AS status_z, TIME_TO_SEC(TIMEDIFF(vw_MaxTimeStamp_20_25_91.MaxTimestamp, vw_MaxTimeStamp_10_11_76.MaxTimestamp)) AS timediff_xy, TIME_TO_SEC(TIMEDIFF(vw_MaxTimeStamp_30_99_104.MaxTimestamp, vw_MaxTimeStamp_20_25_91.MaxTimestamp)) AS timediff_yz, TIME_TO_SEC(TIMEDIFF(vw_MaxTimeStamp_30_99_104.MaxTimestamp, vw_MaxTimeStamp_10_11_76.MaxTimestamp)) AS timediff_xz, CAST(SUBSTR(nve_nr.nve_nvenr, 1, 3) AS UNSIGNED INTEGER) AS filter_collonr, vw_MaxStatusCode.MaxStatusCode AS filter_last_status sdg.sdg_kunde AS filter_kunden_id, sdg.sdg_id AS filter_sdg_id, nve_nr.nve_id AS filter_nve_id, adr_emp.adr_id AS filter_adr_emp_id FROM tbl_nve_nr nve_nr LEFT JOIN tbl_sendungen sdg ON nve_nr.nve_sdg_id = sdg.sdg_id LEFT JOIN tbl_adressen adr_emp ON adr_emp.adr_id = sdg.sdg_adr2_id INNER JOIN tbl_collo_status t_5 ON t_5.colst_nve_id = nve_nr.nve_id LEFT OUTER JOIN vw_MaxTimeStamp_10_11_76 ON vw_MaxTimeStamp_10_11_76.colst_nve_id = nve_nr.nve_id LEFT OUTER JOIN vw_MaxTimeStamp_20_25_91 ON vw_MaxTimeStamp_20_25_91.colst_nve_id = nve_nr.nve_id LEFT OUTER JOIN vw_MaxTimeStamp_30_99_104 ON vw_MaxTimeStamp_30_99_104.colst_nve_id = nve_nr.nve_id LEFT OUTRER JOIN vw_MaxStatusCode ON vw_MaxStatusCode.colst_nve_id = nve_nr.nve_id </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload