.7 performance regression for count queries with MyISAM

[Former user]

If Item is a mapped class, then in .6.7

session.query(Item).count()

generates the query

SELECT count(1) AS count_1 
FROM items

With .7.0, the query generated is instead

SELECT count(*) AS count_1 FROM (SELECT items.column1 AS items_column1, items.column2 AS items_column2, ...) AS anon_1;

With MyISAM, the first query executes instantaneously whereas the second one can take forever for a large table. I killed it after it had been running for 10+ minutes and blocking other queries from running.

In my case, changing the query to

session.query(Item.id).count()

helped a lot.

It would ideal if I could continue to use the first form and somehow manage to get the .6 behavior which allows MyISAM to execute the query without doing any work.

[Mike Bayer]

yup, the decision wasn't entirely easy but the new way works in all cases consistently, simply - the old way did not, as new kinds of bugs kept occurring, more exceptions and left turns kept getting made in count(), all just to avoid using a simple SQL expression that meets the use case exactly. So if you want to optimize a simple count for MyISAM, use func.count():

session.query(func.count(Item.id)).scalar()

If MyISAM is really that stupid about a simple subquery then we should probably add a note to the MySQL docs regarding this.

[Mike Bayer]

Removing milestone: 0.7.1 (automated comment)