On Thursday, August 11, 2011 20:15:43 Colin Wright wrote: > > Here is the sql query that I am running: > > "SELECT transactionId AS _id, payeeId, valueFormatted, memo, postDate, > > name FROM kmmSplits, kmmPayees WHERE (kmmSplits.payeeID = kmmPayees.id > > AND accountId = ? AND txType = 'N') UNION SELECT transactionId, payeeId, > > valueFormatted, memo, postDate, checkNumber FROM kmmSplits WHERE payeeID > > IS NULL AND accountId = ? AND txType = 'N' ORDER BY postDate DESC LIMIT > > 50" > For a start, always use UNION ALL if you know that the two parts of the > query are distinct. UNION is like a set union as performs a DISTINCT to > eliminate duplicates. That DISTINCT operation can significantly slow down > queries. > > In this case, since it will be sorting due to the ORDER BY anyway, the hit > won't be as large as it might have been. > > Also, it's actually an OUTER JOIN and should be rewritten as one unless you > need to support an old version that doesn't support outer joins. > > It looks like it could be really slow in a large data set. If this is done > a lot you'd want an index on kmmSplits(accountid, postDate) (or > kmmSplits(accountid, txType, postDate) if it does lots of type-based > searches and N is uncommon). That way it can do a range scan descending, > preferably with 50 row limits and concatenate the results. >
Yes to the OUTER JOIN rewrite: SELECT transactionId AS _id, payeeId, valueFormatted, memo, postDate, name FROM kmmSplits LEFT OUTER JOIN kmmPayees ON kmmSplits.payeeID = kmmPayees.id WHERE (accountId = ? AND txType = 'N') ORDER BY postDate DESC LIMIT 50 That should get you close, but I would check the results against your earlier query for correctness. Depending on the number of times you want to execute this, make sure to prepare the query only once and bind the accountId as needed. I do not remember if SQLite supports indexing or EXPLAIN, but putting an index on kmmSplits.payeeID and/or accountId may help. Overuse of indexing can hurt in some cases, so check out your other queries after you do this. The EXPLAIN functionality will tell you if the DB is using an index or a full table scan to make things happen. Good Luck. -- Thanks, Fernando Vilas fvi...@iname.com
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ KMyMoney-devel mailing list KMyMoney-devel@kde.org https://mail.kde.org/mailman/listinfo/kmymoney-devel
