This is my first question, since every question I had has already received an answer. please forgive the poor formatting.
The request is executed in 1 ms by itself, which is excellent. It produces about 600,000 results from about 3 million records, while the database is entered at about 10 per second. I know this is not much for the database, so I assume loading is not a problem. I have other big requests that just insert into a file. In particular, adding "SELECT * INTO OUTFILE" takes about 11 hours. It is too long to run the request, and I have no idea why.
Table: container_table
- Primary Key: containerID(bigint), mapID(int), cavityID(int)
- Index: timestamp(datetime)
Table: cont_meas_table
- Primary Key: containerID(bigint), box(int), probe(int), inspectionID(int), measurementID(int)
Table: cavity_map
- Primary Key: mapID(int), gob(char), section(int), cavity(int)
Query:
(SELECT 'containerID','timestamp','mapID','lineID','fp','fpSequence','pocket','cavityID', 'location','inspResult',
'otgMinThickMeasValuePrb2_1','otgMaxThickMeasValuePrb2_1','RatioPrb2_1','otgOORMeasValuePrb2_1',
'otgMinThickMeasValuePrb2_2','otgMaxThickMeasValuePrb2_2','RatioPrb2_2','otgOORMeasValuePrb2_2',
'otgMinThickMeasValuePrb2_3','otgMaxThickMeasValuePrb2_3','RatioPrb2_3')
UNION
(SELECT * INTO OUTFILE 'testcsv.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM
(SELECT containerID, timestamp, groupmeas.mapID, lineID, fp, fpSequence, pocket, cavityID, CONCAT(MIN(section), MIN(gob)) AS location,
inspResult, otgMinThickMeasValuePrb2_1, otgMaxThickMeasValuePrb2_1,
(COALESCE(otgMaxThickMeasValuePrb2_1/NULLIF(CAST(otgMinThickMeasValuePrb2_1 AS DECIMAL(10,5)), 0), 0)) AS RatioPrb2_1,
otgOORMeasValuePrb2_1, otgMinThickMeasValuePrb2_2, otgMaxThickMeasValuePrb2_2,
(COALESCE(otgMaxThickMeasValuePrb2_2/NULLIF(CAST(otgMinThickMeasValuePrb2_2 AS DECIMAL(10,5)), 0), 0)) AS RatioPrb2_2,
otgOORMeasValuePrb2_2, otgMinThickMeasValuePrb2_3, otgMaxThickMeasValuePrb2_3,
(COALESCE(otgMaxThickMeasValuePrb2_3/NULLIF(CAST(otgMinThickMeasValuePrb2_3 AS DECIMAL(10,5)), 0), 0)) AS RatioPrb2_3
FROM
(SELECT dbad.container_table.containerID, dbad.container_table.timestamp, dbad.container_table.mapID, dbad.container_table.lineID, dbad.container_table.fp,
dbad.container_table.fpSequence, dbad.container_table.pocket, dbad.container_table.cavityID, dbad.container_table.inspResult,
CASE WHEN aggMeas.otgMinThickMeasValuePrb2_1 IS NULL
THEN - 1 ELSE aggMeas.otgMinThickMeasValuePrb2_1 END AS otgMinThickMeasValuePrb2_1,
CASE WHEN aggMeas.otgMaxThickMeasValuePrb2_1 IS NULL
THEN - 1 ELSE aggMeas.otgMaxThickMeasValuePrb2_1 END AS otgMaxThickMeasValuePrb2_1,
CASE WHEN aggMeas.otgOORMeasValuePrb2_1 IS NULL
THEN - 1 ELSE aggMeas.otgOORMeasValuePrb2_1 END AS otgOORMeasValuePrb2_1,
CASE WHEN aggMeas.otgMinThickMeasValuePrb2_2 IS NULL
THEN - 1 ELSE aggMeas.otgMinThickMeasValuePrb2_2 END AS otgMinThickMeasValuePrb2_2,
CASE WHEN aggMeas.otgMaxThickMeasValuePrb2_2 IS NULL
THEN - 1 ELSE aggMeas.otgMaxThickMeasValuePrb2_2 END AS otgMaxThickMeasValuePrb2_2,
CASE WHEN aggMeas.otgOORMeasValuePrb2_2 IS NULL
THEN - 1 ELSE aggMeas.otgOORMeasValuePrb2_2 END AS otgOORMeasValuePrb2_2,
CASE WHEN aggMeas.otgMinThickMeasValuePrb2_3 IS NULL
THEN - 1 ELSE aggMeas.otgMinThickMeasValuePrb2_3 END AS otgMinThickMeasValuePrb2_3,
CASE WHEN aggMeas.otgMaxThickMeasValuePrb2_3 IS NULL
THEN - 1 ELSE aggMeas.otgMaxThickMeasValuePrb2_3 END AS otgMaxThickMeasValuePrb2_3,
CASE WHEN aggMeas.otgOORMeasValuePrb2_3 IS NULL
THEN - 1 ELSE aggMeas.otgOORMeasValuePrb2_3 END AS otgOORMeasValuePrb2_3
FROM dbad.container_table
LEFT OUTER JOIN
(SELECT containerID,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 0) AND (meas.probe = 0) THEN meas.value END), - 1) AS otgMinThickMeasValuePrb2_1,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 1) AND (meas.probe = 0) THEN meas.value END), - 1) AS otgMaxThickMeasValuePrb2_1,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 2) AND (meas.probe = 0) THEN meas.value END), - 1) AS otgOORMeasValuePrb2_1,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 0) AND (meas.probe = 1) THEN meas.value END), - 1) AS otgMinThickMeasValuePrb2_2,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 1) AND (meas.probe = 1) THEN meas.value END), - 1) AS otgMaxThickMeasValuePrb2_2,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 2) AND (meas.probe = 1) THEN meas.value END), - 1) AS otgOORMeasValuePrb2_2,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 0) AND (meas.probe = 2) THEN meas.value END), - 1) AS otgMinThickMeasValuePrb2_3,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 1) AND (meas.probe = 2) THEN meas.value END), - 1) AS otgMaxThickMeasValuePrb2_3,
COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 2) AND (meas.probe = 2) THEN meas.value END), - 1) AS otgOORMeasValuePrb2_3
FROM (SELECT containerID, inspectionID, measurementID, probe, value, threshold, calibration FROM dbad.cont_meas_table AS a) AS meas
GROUP BY containerID) AS aggMeas
ON dbad.container_table.containerID = aggMeas.containerID) AS groupmeas
INNER JOIN
dbad.cavity_map
ON groupmeas.mapID=dbad.cavity_map.mapID AND
groupmeas.cavityID=dbad.cavity_map.cavity
WHERE timestamp LIKE '2014-08-29%'
AND otgMinThickMeasValuePrb2_1 BETWEEN 1 AND 499
AND otgMinThickMeasValuePrb2_2 BETWEEN 1 AND 499
AND otgMinThickMeasValuePrb2_3 BETWEEN 1 AND 499
AND otgMaxThickMeasValuePrb2_1 BETWEEN 1 AND 499
AND otgMaxThickMeasValuePrb2_2 BETWEEN 1 AND 499
AND otgMaxThickMeasValuePrb2_3 BETWEEN 1 AND 499
GROUP BY containerID) AS outside)
COUNT() DISTINCT "%" WHERE timestamp LIKE '2014-08-29%', . , .
EDIT:
WHERE timestamp >= '2014-08-29'
AND timestamp < '2014-08-29' + INTERVAL 1 DAY
. , , - .