Simple SQL perfoemance issues

[Grzegorz Dzień]

I have an app with following code:

    curr_val = db(db.pushes).select().last().kolik
    if db(db.pushes.created_on).select().last().created_on.date() < datetime.datetime.now().date():
        db.pushes.insert(kolik=1)
    else:
        db.pushes.insert(kolik=curr_val+1)

When DB gets to 6428 (last time I've checked) this took 9 seconds on mysql, after I deleted all records it ends up within 1 second. As the DB grows this code gets slower and slower.
Can someone point me as to what I am doing wrong?

[Anthony]

First, note that without specifying an orderby, the order of the records is not guaranteed to follow any particular rule, so you can't be sure which record .last() will return.

Second, your current query is returning all records from the database, which involves parsing each record into a Row object. Depending on the data in each record and the number of records, this can get slow. However, you only want a single record (it appears you want the one with the most recent created_on date), so you should instead use orderby and limitby:

curr_record = db(db.pushes).select(orderby=~db.pushes.created_on, limitby=(0, 1)).first()
if current_record.created_on.date() < datetime.datetime.now().date():
    etc.

To speed things up further, you can define an index on the created_on field.

Anthony

[Grzegorz Dzień]

Thanks a lot! That solved my issue.