[Akonadi] [Bug 351475] Optimization of an SQL query

[Daniel Vrátil]

https://bugs.kde.org/show_bug.cgi?id=351475

Daniel Vrátil <dvratil at kde.org> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |dvratil at kde.org

--- Comment #2 from Daniel Vrátil <dvratil at kde.org> ---
Hi,

this is a query to update collection statistics (i.e. number of read/unread
emails in a folder). In Akonadi 1.13.0 (and before) we were calculating this
expensive statistics all the time. Not just whenever something changed, but we
calculated it again for each client that asked for it, which was can be over 10
clients at once.

I actually fixed this in the 1.13 branch after release but we never made 1.13.1
release with this fix. The fix is also included in current 15.08.0 release. We
now have a centralized cache for the statistics, which is only updated when
necessary and caches the results until next change, so we don't run the SQL
query each time for every client.

The query now looks slightly different, in addition to the flag count it also
calculates total size (as in bytes) of all items in the collection. Originally
it were two queries, I wrote this ironically because I found that the JOIN is
slow and wanted to avoid doing to JOINs. Maybe with your solution we could
afford running two queries to get flags count and size while still being faster
than this.

This is the current query we have:

SELECT count(DISTINCT PimItemTable.id), sum(PimItemTable.size), sum(CASE WHEN
PimItemFlagRelation.rightId = $1 OR PimItemFlagRelation.rightId = $2 THEN 1
ELSE 0 END) FROM PimItemTable LEFT JOIN PimItemFlagRelation ON PimItemTable.id
= PimItemFlagRelation.leftId
WHERE PimItemTable.collectionId = $3

-- 
You are receiving this mail because:
You are the assignee for the bug.