title: Complex SQL optimization vs. general-purpose language
body: <p>How might I optimize this query? The schema:</p>
  
  <pre><code>mysql&gt; show columns from transactionlog;
  +---------------+-------------------------------------------+------+-----+---------+----------------+
  | Field         | Type                                      | Null | Key | Default | Extra          |
  +---------------+-------------------------------------------+------+-----+---------+----------------+
  | id            | int(11)                                   | NO   | PRI | NULL    | auto_increment |
  | transactionid | varchar(10)                               | NO   | MUL | NULL    |                |
  | queryid       | tinyint(4)                                | NO   |     | NULL    |                |
  | tableid       | varchar(30)                               | NO   | MUL | NULL    |                |
  | tupleid       | int(11)                                   | NO   |     | NULL    |                |
  | querytype     | enum('select','insert','delete','update') | NO   |     | NULL    |                |
  | schemaname    | varchar(20)                               | YES  |     | NULL    |                |
  | partition     | tinyint(3) unsigned                       | YES  |     | NULL    |                |
  +---------------+-------------------------------------------+------+-----+---------+----------------+
  8 rows in set (0.04 sec)
  </code></pre>
  
  <p>The query:</p>
  
  <pre><code>select concat(weight, ' ', ids, '\n')
  from (
    select
      tableid,
      tupleid,
      group_concat(id separator ' ') as ids,
      (
        select count(distinct transactionid)
        from transactionlog
        where transactionid in (
          select transactionid
          from transactionlog
          where (tableid, tupleid, querytype) =
                (t.tableid, t.tupleid, 'update')
          group by transactionid
          having count(*) &gt; 0
        )
      ) weight
    from transactionlog t
    group by tableid, tupleid
    having weight &gt; 0 and count(*) &gt; 1
  ) u;
  </code></pre>
  
  <p>This is the output of EXPLAIN and mk-visual-explain:</p>
  
  <pre><code>+----+--------------------+----------------+-------+---------------+---------------+---------+-----------+------+------------------------------
  ----------------+
  | id | select_type        | table          | type  | possible_keys | key           | key_len | ref       | rows | Extra                                        |
  +----+--------------------+----------------+-------+---------------+---------------+---------+-----------+------+----------------------------------------------+
  |  1 | PRIMARY            | &lt;derived2&gt;     | ALL   | NULL          | NULL          | NULL    | NULL      |   13 |                                              |
  |  2 | DERIVED            | t              | ALL   | NULL          | NULL          | NULL    | NULL      |   68 | Using filesort                               |
  |  3 | DEPENDENT SUBQUERY | transactionlog | index | NULL          | transactionid | 12      | NULL      |   68 | Using where; Using index                     |
  |  4 | DEPENDENT SUBQUERY | transactionlog | ref   | tableid       | tableid       | 36      | func,func |    2 | Using where; Using temporary; Using filesort |
  +----+--------------------+----------------+-------+---------------+---------------+---------+-----------+------+----------------------------------------------+
  Table scan
  rows           13
  +- DERIVED
     table          derived(t,transactionlog,temporary(transactionlog))
     +- DEPENDENT SUBQUERY
        +- DEPENDENT SUBQUERY
        |  +- Filesort
        |  |  +- TEMPORARY
        |  |     table          temporary(transactionlog)
        |  |     +- Filter with WHERE
        |  |        +- Bookmark lookup
        |  |           +- Table
        |  |           |  table          transactionlog
        |  |           |  possible_keys  tableid
        |  |           +- Index lookup
        |  |              key            transactionlog-&gt;tableid
        |  |              possible_keys  tableid
        |  |              key_len        36
        |  |              ref            func,func
        |  |              rows           2
        |  +- Filter with WHERE
        |     +- Index scan
        |        key            transactionlog-&gt;transactionid
        |        key_len        12
        |        rows           68
        +- Filesort
           +- Table scan
              rows           68
              +- Table
                 table          t
  </code></pre>
  
  <p>That is a lot of work. I can write the equivalent logic in Python while making a single pass:</p>
  
  <pre><code>results = query("""
    select tableid, tupleid, transactionid, id, querytype
    from transactionlog_2warehouse
  """)
  _tab, _tup = None
  ids = []
  weight = 0
  saw_upd = False
  for tab, tup, txn, id, qt in results:
    if (_tab, _tup) != (tab, tup):
      if len(ids) &gt; 1 and weight &gt; 0:
        print weight, ids
      weight = 0
      ids = []
      _txn = None
    if _txn != txn:
      saw_upd = False
    if qt == 'update' and not saw_upd:
      weight += 1
      saw_upd = True
    ids += [id]
  </code></pre>
  
  <p>Is it possible to achieve the Python single-pass performance using pure SQL? Thanks in advance!</p>