[Solved] sqlalchemy query comparing function on multiple columns to another value

EverSQL Database Performance Knowledge Base

sqlalchemy query comparing function on multiple columns to another value

Using sqlalchemy, how do i set up a filter that is comparing the value of one column to the value of a function run on multiple columns.

The goal is to write an orm query that selects objects where dt_added + max_age seconds < now.

If I were to just write the raw sql, this is the statement I'd be going for:

SELECT *
FROM sometable s
WHERE DATE_ADD(s.dt_added,interval max_age second) < NOW()

The solutions I looked at suggest using raw python datetime objects, but that won't work here because different rows have different values for max_age. The math has to happen on the server side.

class SomeObject(Base):
    __tablename__ = 'sometable'
    some_id = Column(Integer, primary_key=True)
    modified = Column(DateTime, nullable=False,
        server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
    max_age = Column(Integer)

Sample data:

id, dt_added, max_age
 1, 2015-12-30 12:15:00, 3600
 2, 2015-12-30 12:15:00, 60

If the current datetime was '2015-12-30 12:20:00' it would return row 2, but not row 1.

I am able to force the issue using raw text, but I'd rather do it completely through the orm.

from sqlalchemy.sql import func
from sqlalchemy.sql.expression import text

query = ses.query(
    SomeObject
).filter(
    func.date_add(
        SomeObject.created,
        text('INTERVAL max_age second')
    ) < datetime.datetime.now()

Many thanks.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
Optimal indexes for this query:
ALTER TABLE `sometable` ADD INDEX `sometable_idx_date_age` (`date_add_dt_added_interval_max_age`);
The optimized query:
SELECT
        * 
    FROM
        sometable s 
    WHERE
        s.date_add_dt_added_interval_max_age < NOW()

Related Articles



* original question posted on StackOverflow here.