Re: [Snowy] Issues with MySQL

[Sandy Armstrong <sanfordarmstrong gmail com>]

On Mon, Jun 1, 2009 at 10:55 AM, Brad Taylor <brad getcoded net> wrote:
> Hey,
>
>> >  1) Allow notes with the same title
>>
>> Well I guess we could enforce this ourselves with a query.  Not really
>> sure what the impact would be there.  Would get a hit on every edit to
>> title (fairly rare), every note change upload via sync (probably not
>> too bad), and every new note creation (we might already do that
>> anyway).
>
> This would be a very slow query.  If we have to do it this way, I'm
> alright with it as it's a low user impact, but it will get much slower
> as Snowy becomes more popular.

Will the query really get a lot slower as the DB grows, even though
you're only looking at one user's notes?

>> >  or
>> >
>> >  2) Bound the length of the Title and use a CharField.
>> >
>> > To me, #2 seems like a better choice.
>>
>> That's just not an accurate representation of the note data anymore,
>> which is my main concern.  I assume we don't want to go back to a
>> solution where the title is part of the content field.
>
> Definitely not.  It makes building the note list a major pain in the ass
> and ultimately slow as molasses.
>
>> If the suggestion I made above about manually enforcing uniqueness is
>> not doable, how bad would it be to have the title potentially
>> duplicated?
>
> I thought Tomboy doesn't allow notes with the same name?  If we allow
> multiple names, the note list on the left is going to be weird also.

I meant duplicated in two database columns, as shown directly below:

>> We could replace the current title field with two fields:
>> bounded_title and full_title.  bounded_title could have uniqueness
>> set, be used for title lists, etc.  full_title could be used when
>> showing the complete contents.  full_title could potentially be blank
>> if it would be the same as bounded_title, but that might add more
>> queries when showing content, so maybe no?  Thoughts?
>
> Okay, let's say for the sake of argument that len(bounded_title) was 10.
>
> a = Note.objects.all()[1]
> a.bounded_title = 'abcdefghij'
> a.full_title = 'abcdefghijklmnopqrstuvwxyz'
>
> b = Note.objects.all()[2]
> b.bounded_title = 'abcdefghij'
> b.full_title = 'abcdefghijxyz12345566788'
>
> so our uniqueness breaks very quickly.  And it's clear that if we picked
> a higher number, say 256, that we're still not guaranteeing uniqueness.
>
> While this approach may work in Tomboy where the worst penalty is on the
> user, when we put it on the web, everyone is impacted by a user who
> writes an Homerian epic in the note title.

Okay.  Maybe we should just not guarantee title uniqueness in the
Snowy DB.  If you think about it, when we generate titles for new
notes and when we attempt to rename notes in Funcooker (TM), we're
going to have to do a query anyway.  In the case of new notes we'll
have to create a new unique title, and in the case of renames we'll
want to give useful error messages instead of just a DB error.  So is
it worth it to guarantee title uniqueness at the DB level?  I'm not
sure what the benefit is, but I'm probably missing something obvious.
If a duplicate title somehow snuck in via sync or something, I don't
think anything would break.

Sandy