T7846: akonadi > MySQL ERD Review

[Pablo Sanchez]

pablo added a comment.


  //I have attached the latest ERD's.  These are printer-friendly (no color).  I had to jig the ERD tool's XML file for MySQL.//
  
  Hey Dan,
  
  Sorry for the delay.
  
  Overall, if we were to equate the ERD to syntax versus semantics, syntactically it's in relatively good shape - more below.
  
  Thank you for the explanation on **[PartTypeTable]**.
  
  On the //Associative Tables//, thank you for your response.  Let's leave them as they are defined.  We have some inconsistencies but the net effect is the same:  **Primary Key** versus **Unique Key**.   From my experience, the optimizer should treat a **Primary Key** like a **Unique Key**
  
  At some point we should change the following tables to define a **Primary Key** rather than a **Unique Key**:
  
  - RelationTable
  - TagRemoteIdResourceRelationTable
  
  On **[CollectionTable]**, the (extremely) general guideline is if we are returning more than 10% of the data, it's more efficient to perform an index scan.  The actual percentage varies though.  Some //tricks// can be applied so that if predominantly a subset of columns are always returned, an index can be used to create a //mini-table//  The Optimizer/Planner is generally smart enough to perform a full index scan of this structure.
  
  This method can be a bit fragile because if dependent queries add more columns, they //fall off// of the index and instead need to perform their full scan against the table.  I generally don't like to use this //piggyback// hack.  It's a bit brittle.
  
  **Recap**
  
  - Eventually change //Associative Tables// that have **Unique Key** to **Primary Key** (like the rest)
  - Drop unnecessary indexes from **[CollectionTable]**
  
  Thoughts?
  
  F5690877: akonadi-mysql.pdf <https://phabricator.kde.org/F5690877>
  
  F5690878: akonadi-mysql-birds-eye.pdf <https://phabricator.kde.org/F5690878>

TASK DETAIL
  https://phabricator.kde.org/T7846

To: pablo
Cc: dvratil, kde-pim, pablo
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/kde-pim/attachments/20180203/75a11340/attachment.html>