Databases
StackOverflow2013
Postsdbo
POSelecting rows where column value changed from previous row, user variables, innodb
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting rows where column value changed from previous row, user variables, innodb
    text
    copied!<p>I have a problem similar to <a href="https://stackoverflow.com/questions/6560000/sql-selecting-rows-where-column-value-changed-from-previous-row">SQL: selecting rows where column value changed from previous row</a></p> <p>The accepted answer by <a href="https://stackoverflow.com/users/344949/ypercube">ypercube</a> which i adapted to </p> <pre><code>CREATE TABLE `schange` ( `PersonID` int(11) NOT NULL, `StateID` int(11) NOT NULL, `TStamp` datetime NOT NULL, KEY `tstamp` (`TStamp`), KEY `personstate` (`PersonID`, `StateID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `states` ( `StateID` int(11) NOT NULL AUTO_INCREMENT, `State` varchar(100) NOT NULL, `Available` tinyint(1) NOT NULL, `Otherstatuseshere` tinyint(1) NOT NULL, PRIMARY KEY (`StateID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SELECT COALESCE((@statusPre &lt;&gt; s.Available), 1) AS statusChanged, c.PersonID, c.TStamp, s.*, @statusPre := s.Available FROM schange c INNER JOIN states s USING (StateID), (SELECT @statusPre:=NULL) AS d WHERE PersonID = 1 AND TStamp &gt; "2012-01-01" AND TStamp &lt; "2013-01-01" ORDER BY TStamp ; </code></pre> <p>The query itself worked just fine in testing, and with the right mix of temporary tables i was able to generate reports with daily sum availability from a huge pile of data in virtually no time at all. The real problem comes in when i discovered that the tables where using the MyISAM engine, which we have completely abandoned, recreated the tables to use InnoDB, and noticed the query no longer works as expected.</p> <p>After some bashing head into wall i have discovered that MyISAM seems to go over the columns each row in order (selecting statusChanged before updating @statusPre), while InnoDB seems to do all the variable assigning first, and only after that it populates result rows, regardless if the assigning happens in the select or where clauses, in functions (coalesce, greater etc), subqueries or otherwise.</p> <p>Trying to accomplish this in a query without variables seems to always end the same way, a subquery requiring exponentially more time to process the more rows are in the set, resulting in a excrushiating minutes (or hours) long wait to get beginning and ending events for one status, while a finished report should include daily sums of multiple.</p> <p>Can this type of query work on the InnoDB engine, and if so, how should one go about it? or is the only feasible option to go for a database product that supports WITH statements?</p>
 

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