Cameron Eagans cweagans
cweagans
/ gist:1651186
Created
January 21, 2012 03:51
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| pic.nid, | |
| pic.sid as "original_state", | |
| pis.name as "original_state_name", | |
| pic.comment_number, | |
| ( | |
| SELECT | |
| pic2.sid | |
| FROM | |
| project_issue_comments pic2 |
cweagans
/ gist:1651192
Created
January 21, 2012 03:53
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| mysql> SELECT | |
| -> pic.nid, | |
| -> pic.sid as "original_state", | |
| -> pis.name as "original_state_name", | |
| -> pic.comment_number, | |
| -> ( | |
| -> SELECT | |
| -> pic2.sid | |
| -> FROM | |
| -> project_issue_comments pic2 |
cweagans
/ gist:1651278
Created
January 21, 2012 04:23
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| pic.nid, | |
| ( | |
| SELECT | |
| pic2.sid | |
| FROM | |
| project_issue_comments pic2 | |
| WHERE | |
| pic2.nid = 8 AND | |
| pic2.comment_number = pic.comment_number - 1 |
cweagans
/ gist:1651279
Created
January 21, 2012 04:24
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| mysql> SELECT | |
| -> pic.nid, | |
| -> ( | |
| -> SELECT | |
| -> pic2.sid | |
| -> FROM | |
| -> project_issue_comments pic2 | |
| -> WHERE | |
| -> pic2.nid = 8 AND | |
| -> pic2.comment_number = pic.comment_number - 1 |
cweagans
/ gist:1651319
Created
January 21, 2012 04:43
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| mysql> SELECT | |
| -> * | |
| -> FROM | |
| -> (SELECT | |
| -> pic.nid, | |
| -> ( | |
| -> SELECT | |
| -> pic2.sid | |
| -> FROM | |
| -> project_issue_comments pic2 |
cweagans
/ gist:1651335
Created
January 21, 2012 04:50
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| n.nid, n.created, issuedata.timestamp as first_review | |
| FROM | |
| (SELECT | |
| pic.nid, | |
| ( | |
| SELECT | |
| pic2.sid | |
| FROM | |
| project_issue_comments pic2 |
cweagans
/ gist:1651336
Created
January 21, 2012 04:51
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| mysql> SELECT | |
| -> n.nid, n.created, issuedata.timestamp as first_review | |
| -> FROM | |
| -> (SELECT | |
| -> pic.nid, | |
| -> ( | |
| -> SELECT | |
| -> pic2.sid | |
| -> FROM | |
| -> project_issue_comments pic2 |
cweagans
/ gist:1651625
Created
January 21, 2012 06:06
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| pic.nid, | |
| ( | |
| SELECT | |
| pic2.sid | |
| FROM | |
| project_issue_comments pic2 | |
| WHERE | |
| pic2.nid = pic.nid AND | |
| pic2.comment_number = pic.comment_number - 1 |
cweagans
/ gist:1651627
Created
January 21, 2012 06:06
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| pic.nid, | |
| ( | |
| SELECT | |
| pic2.sid | |
| FROM | |
| project_issue_comments pic2 | |
| WHERE | |
| pic2.nid = pic.nid AND | |
| pic2.comment_number = pic.comment_number - 1 |
cweagans
/ gist:1651707
Created
January 21, 2012 06:32
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| mysql> explain SELECT * FROM (SELECT pic.nid, ( SELECT pic2.sid FROM project_issue_comments pic2 WHERE pic2.nid = pic.nid AND pic2.comment_number = pic.comment_number - 1 ) as "original_state", ( SELECT pis2.name FROM project_issue_state pis2 WHERE pis2.sid = original_state ) as "original_state_name", pic.sid as "new_state", pis.name as "new_state_name", pic.comment_number, pic.timestamp FROM project_issue_comments pic JOIN project_issue_state pis ON pic.sid = pis.sid WHERE pic.nid = 8) as issuedata WHERE issuedata.original_state <> issuedata.new_state; | |
| +----+--------------------+------------+--------+------------------------------+---------------+---------+-----------------------+------+-------------+ | |
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | |
| +----+-- |