Slow query result

[willy140198]

so i have query like this

SELECT * 
FROM lms.transactions lmsTran
JOIN mifos.m_loan_transaction mifosTran
on lmsTran.external_txn_id = mifosTran.external_id
WHERE lmsTran.created_at > start_date(?) and lmsTran.created_at < end_date(?)
limit 1

and the result take so long, more than 1 minute with 32k data, and this query using mysql and postgres at once. and start_date is our function to parse to parset_time dont mind it. are you have any sugestion? thank you!

[cube2222]

Depends on what start_date/end_date are referencing in the arguments.

I suppose the predicates (lmsTran.created_at > start_date(?) and lmsTran.created_at < end_date(?)) aren't getting pushed down to the underlying database, and it has to scan the whole mifos.m_loan_transaction table for each transaction. You could try changing the JOIN order (mifos.m_loan_transaction mifosTran JOIN lms.transactions lmsTran).

I'll try to add some kind of query plan printing in the near future.

[willy140198]

oh i see, that's make sense, thank you!

[cube2222]

Closing for now, feel free to reopen.