I’m using SQLAlchemy with a MySQL database and I’d like to count the rows in a table (roughly 300k). The SQLAlchemy count function takes about 50 times as long to run as writing the same query directly in MySQL. Am I doing something wrong?
# this takes over 3 seconds to return session.query(Segment).count()
SELECT COUNT(*) FROM segments; +----------+ | COUNT(*) | +----------+ | 281992 | +----------+ 1 row in set (0.07 sec)
The difference in speed increases with the size of the table (it is barely noticeable under 100k rows).
session.query(Segment.id).count() instead of
session.query(Segment).count() seems to do the trick and get it up to speed. I’m still puzzled why the initial query is slower though.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Unfortunately MySQL has terrible, terrible support of subqueries and this is affecting us in a very negative way. The SQLAlchemy docs point out that the “optimized” query can be achieved using
Return a count of rows this Query would return.
This generates the SQL for this Query as follows:SELECT count(1) AS count_1 FROM ( SELECT <rest of query follows...> ) AS anon_1
For fine grained control over specific columns to count, to skip the
usage of a subquery or otherwise control of the FROM clause, or to use
other aggregate functions, use func expressions in conjunction with
from sqlalchemy import func # count User records, without # using a subquery. session.query(func.count(User.id)) # return count of user "id" grouped # by "name" session.query(func.count(User.id)). group_by(User.name) from sqlalchemy import distinct # count distinct "name" values session.query(func.count(distinct(User.name)))
The reason is that SQLAlchemy’s count() is counting the results of a subquery which is still doing the full amount of work to retrieve the rows you are counting. This behavior is agnostic of the underlying database; it isn’t a problem with MySQL.
The SQLAlchemy docs explain how to issue a count without a subquery by importing
session.query(func.count(User.id)).scalar() >>>SELECT count(users.id) AS count_1 nFROM users')
It took me a long time to find this as the solution to my problem. I was getting the following error:
126 (HY000): Incorrect key file for table ‘/tmp/#sql_40ab_0.MYI’; try
to repair it
The problem was resolved when I changed this:
query = session.query(rumorClass).filter(rumorClass.exchangeDataState == state) return query.count()
query = session.query(func.count(rumorClass.id)).filter(rumorClass.exchangeDataState == state) return query.scalar()