title: Using sqlalchemy session to execute sql DRASTICALLY slows execution time
body: <p>I have a rather long query (was 7 joins, now is 7 subselects because in raw sql 7 subselects was considerably faster-- I don't even know when the 7 joins would have finished if I'd let it run, but longer than 1 min versus .05-.1 seconds with subselects)</p>
  
  <p>When I run it on the db it takes, as I said, .05-.1 seconds to execute. Simply using <code>session.execute()</code> slows it down to over a minute!</p>
  
  <p>Is there anything I can do?</p>
  
  <p>Let me know if you need more info-- I kind of suspect this is a general sqlalchemy thing-- like maybe sqlalchemy is setting up a query plan instead of just letting mysql do it? Or...?</p>
  
  <p>EDIT: ran explain on both and they seem identical except that sqlalchemy adds a "using temporary; using filesort" to the <code>extra</code> column. Is that what's slowing it down? How do I stop it from doing that?</p>
  
  <p>EDIT 2: DEFINITELY sqlalchemy. I tried using a MySQL cursor to execute instead of an SA session and got the same .05 second runtime.</p>
  
  <p>EDIT 3:</p>
  
  <p>The code to create our engine:</p>
  
  <pre><code>engine_ro = create_engine(
      config.ro_database_url, #string with username, password, db
      pool_size=config.database_pool_size, #int
      max_overflow=config.database_max_overflow, #int
      pool_timeout=config.database_timeout, # int
      echo=config.database_echo, #False
      echo_pool=config.database_echo, #same as echo #False
      listeners=[GoneAway()] if config.database_use_listeners else None)
  </code></pre>
  
  <p>where <code>GoneAway()</code> is a method that executes a <code>SELECT 1</code> to check the connection.</p>
  
  <p>To create the session object:</p>
  
  <pre><code>SessionRO = scoped_session(sessionmaker(bind=engine_ro, autocommit=False))
  </code></pre>
  
  <p>where <code>scoped_session</code> and <code>sessionmaker</code> are sqlalchemy functions.</p>
  
  <p>Then, the code that executes the query:</p>
  
  <pre><code>session = SessionRO()
  results = session.execute(sql, params)
  </code></pre>
  
  <p>EDIT 4: In case anyone is wondering, if I comment out the <code>listeners</code> bit, it's still slow. As well if I just use <code>sessionmaker</code> without scoped_session.</p>