[Kmymoney-devel] Anyone good with SQL?

[Colin Wright]

> 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.

Thanks,

Colin Wright