MySQL query hanging

Question

I have a mysql query, which runs however it just hangs and doesn't stop. I'm unsure where the query is breaking.

SELECT
 TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', f.Datestamp) AS Epoch,
 f.value AS `Usage`,
 q.Name AS Quantity,
 q.QuantityID,
 units,
 PrimeDataItem,
 dd.Description
FROM SourceChannels c
 JOIN UsageHhourly f ON c.ChanID = f.ChanID
 JOIN Quantities q ON c.QuantityID = q.QuantityID
 LEFT JOIN DigitalDescriptions dd ON ((c.DigitalDescriptionID = dd.DigitalDescriptionID)
 AND f.value = (CASE WHEN dd.Value REGEXP '^[0-9]' = 1 then dd.Value ELSE -1 END))
WHERE
 c.EquipmentID = 1496
 AND f.DateStamp >= '2016-12-28 00:00:00'
 AND f.DateStamp < '2017-01-04 00:00:00'
ORDER BY q.QuantityID, f.datestamp

With respect, you haven't given nearly enough information for us to help you. Please read this. http://meta.stackoverflow.com/a/271056/ Then, please [edit] your question. — O. Jones, Jan 23 '17 at 11:35

score 0 · Answer 1 · answered Jan 23 '17 at 11:50

0

The question does not provide more en-lighting information. Try to alter your query like in the below example.

SELECT
 TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `f`.`Datestamp`) AS `Epoch`,
 `f`.`value` AS `Usage`,
 `q`.`Name` AS `Quantity`,
 `q`.`QuantityID`,
 `c`.`units`,
 `c`.`PrimeDataItem`,
 `dd`.`Description`
FROM `SourceChannels` `c`
 INNER JOIN `UsageHhourly` `f` ON `c`.`ChanID` = `f`.`ChanID`
 INNER JOIN `Quantities` `q` ON `c`.`QuantityID` = `q`.`QuantityID`
 LEFT JOIN `DigitalDescriptions` `dd` ON `c`.`DigitalDescriptionID` = `dd`.`DigitalDescriptionID`
WHERE
`f`.`value` = IF((dd.Value REGEXP '^[0-9]'),1,-1)
 AND `c`.`EquipmentID` = 1496
 AND `f`.`DateStamp` BETWEEN '2016-12-28 00:00:00' AND '2017-01-04 00:00:00'
 ORDER BY `q`.`QuantityID`, `f`.`datestamp`;

answered Jan 23 '17 at 11:50

Caution: Moving something from `LEFT JOIN...ON` to `WHERE` may change the resultset. – Rick James Jan 23 '17 at 17:25
@RickJames yes it can, that is why i wrote that the question does not provide more en-lighting information. There are no information on the MySQL tables used in the query. You are right mentioning this. – Jan 23 '17 at 18:36
@RickJames i think you would agree that the failing part in this query is `LEFT JOIN DigitalDescriptions dd ON ((c.DigitalDescriptionID = dd.DigitalDescriptionID) AND f.value = (CASE WHEN dd.Value REGEXP '^[0-9]' = 1 then dd.Value ELSE -1 END))` – Jan 23 '17 at 18:38
Maybe `f.value` is 1 or -1 depending on whether `dd.Value` starts with a digit? Seems strange, but not likely to cause a 'hang'. – Rick James Jan 23 '17 at 18:44
@RickJames can you explain what the OP try to achieve with this part? `LEFT JOIN DigitalDescriptions dd ON ((c.DigitalDescriptionID = dd.DigitalDescriptionID) AND f.value = (CASE WHEN dd.Value REGEXP '^[0-9]' = 1 then dd.Value ELSE -1 END))` – Jan 23 '17 at 23:57
Let's how the OP answers that question. I gave a lame answer 5 hours ago. – Rick James Jan 24 '17 at 00:33
@RickJames my opinion (judging by the syntax used in `LEFT JOIN`) is that the OP needs to take a look at these http://stackoverflow.com/questions/21791478/join-tables-on-columns-of-composite-foreign-primary-key-in-a-query, https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_generated_stored_column, https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_generated_virtual_column, https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/ . – Jan 24 '17 at 08:25

MySQL query hanging

1 Answers1