[prev in list] [next in list] [prev in thread] [next in thread] 

List:       kde-pim
Subject:    Re: [Kde-pim] Akonadi db schema change needed to store search folder queryStrings
From:       Milian Wolff <mail () milianw ! de>
Date:       2012-02-24 14:50:02
Message-ID: 1647588.0RU8uOuJM6 () minime
[Download RAW message or body]

[Attachment #2 (multipart/signed)]


On Friday 24 February 2012 14:18:35 Will Stephenson wrote:
> On Friday 24 Feb 2012 13:28:06 you wrote:
> > On Friday 24 February 2012 09:52:37 Will Stephenson wrote:
> > > On Thursday 23 Feb 2012 22:26:49 Shaheed Haque wrote:
> > > > Is the extra storage really a concern? I suppose the pimitemtable is
> > > > probably going to have most entries by far. That seems to have a
> > > > handful of columns which might be strings (and 9 in total). Even if I
> > > > had a million records, we are talking a handful of MB.
> > > > 
> > > > That seems perfectly reasonable...Or have I misunderstood?
> > > 
> > > Multiplying the number of rows by the number of QString columns and
> > > summing
> > > it across all tables gives me (2 imap accounts, about 2.5GB mail in
> > > total
> > > on the servers) an additional 1455755 bytes for the storage. Then I
> > > assume
> > > indexed columns count double, which gives another 872906 * 2 bytes as
> > > there
> > > are 2 indices on parttable.name (which contains a lot of repeated
> > > entries,
> > > perhaps it would make sense to normalize this out into its own table?).
> > > 
> > > That's 3201567 extra bytes in theory.  I'm not a database guy so don't
> > > know
> > > how that translates into real extra storage, memory footprint (indexes
> > > stay
> > > in memory, right?) and performance.  Can someone better informed
> > > comment?
> > 
> > VARBINARY has variable length, hence it will only use as much as it
> > actually needs. The number you pass it is the maximum size.
> 
> I'm on kde-pim@, no need to CC me.

bad habit (answer vs answer to mailing list shortcut ;-)) - sorry

> In my reading of the manual, VARBINARY has the same storage requirements as
> VARCHAR, which means, it requires length(data) bytes, plus one byte for len
> <=255, else plus 2 bytes.  It's in the manual.  I suppose it uses either a
> byte or a short for the field length.

doh - I should have read your initial mails in more detail :) you are right of 
course. Anyhow, from a DB-pov it doesn't really make sense to put *all* 
QString into a VARCHAR imo. Rather, it depends on the actual size. A field 
that stores a query should imo be one of the TEXT types. For fields that store 
some often-used lookup value (remoteid, name, etc. come to mind), a VARCHAR 
would of course be better for performance reasons, yet one must ensure that 
the values stored are really never larger than what can be stored in the DB. 
Such silent problems like you describe here might happen in other places as 
well - yikes!

Oh and your 3201567 Bytes don't sound that much to me I have to admit... It's 
just 3MB - who cares?

PS: see also http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
http://forums.mysql.com/read.php?24,105964,105964#msg-105964 and 
http://stackoverflow.com/questions/2023481/mysql-large-varchar-vs-text
-- 
Milian Wolff
mail@milianw.de
http://milianw.de
["signature.asc" (application/pgp-signature)]

_______________________________________________
KDE PIM mailing list kde-pim@kde.org
https://mail.kde.org/mailman/listinfo/kde-pim
KDE PIM home page at http://pim.kde.org/

[prev in list] [next in list] [prev in thread] [next in thread]