Why is SQLAlchemy count() much slower than the raw query?

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()

However:

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).

Update

Using 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.

Contents hide

Answers:

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.

Method 1

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 query(func.count(Segment.id)):

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
query(), i.e.:
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)))

Method 2

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 func from sqlalchemy.

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

>>>SELECT count(users.id) AS count_1 nFROM users')

Method 3

It took me a long time to find this as the solution to my problem. I was getting the following error:

sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError)
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()

to this:

query = session.query(func.count(rumorClass.id)).filter(rumorClass.exchangeDataState == state)
return query.scalar()

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating