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

[Marc Cousin]

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

--- Comment #5 from Marc Cousin <cousinmarc at gmail.com> ---
The 1.13 still exhibits the problem.

Anyway, if you need the query in the comment 3, yeah, caching it is the best
thing to do.

The problem is that we are joining «a lot» (thousands) of records. It consumes
either CPU (if done through hash join or merge join) or IO (if the planner
choses a nested loop). For people with SSDs, nested loop is the way to go. But
as most PostgreSQL and MySQL instances are embedded with akonadi, they have the
«basic» tuning in place, and don't know that :)

The solutions that are usually applied when a query like this really has to be
made fast no matter what are all through denormalization:

* get rid of the PimItemFlagRelation table, and store the pimflags directly in
PimItemTable (using an array, or a json for instance). I don't think mysql has
arrays anyway. It often makes the code complicated...
* simply maintain the totals in the database (with triggers). Same here, as far
as I remember, MySQL's trigger aren't very advanced

Usually, these are bad ideas. Denormalization is really a last-resort solution,
as it makes code more complicated.

akonadi 15.08 is in arch testing. I will give it a look too.

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