How can I optimize DAL web2py query?

Question

result = db((db.company.location.belongs(locations)) &
            (db.company.emp_id.belongs(employee_ids)) &
            (db.company.type.belongs(types))).select()

locations is list of location ids

employee_ids is list of employee ids

types = ['General', 'office', 'e-commerce']

This query return 60,000 records and takes 1 minute to complete. How can I optimize it or split it?

Answer 1

I found myself a solution.

Company table has 20 columns. It is not specified in query that which fields to select, query returns 60,000 records each having 20 fields.

I optimized query by selecting only those column which are needed.

I needed only id and name. So I changed the query to following, now query takes only 10 seconds (previous was 60 seconds):

result = db((db.company.location.belongs(locations)) &
            (db.company.emp_id.belongs(employee_ids)) &
            (db.company.type.belongs(types))).select(db.company.id, db.company.name)