Re: [Snowy] Issues with MySQL

[Sandy Armstrong <sanfordarmstrong gmail com>]

On Sun, May 31, 2009 at 6:10 PM, Brad Taylor <brad getcoded net> wrote:
> On Sun, 2009-05-31 at 20:54 -0400, Og Maciel wrote:
>> On Sun, May 31, 2009 at 8:39 PM, Sandy Armstrong
>> <sanfordarmstrong gmail com> wrote:
>> > That being said, I don't know what error Og was experiencing that required the change he
>> > made.  We should discuss this before adding in any hacks.
>>
>> The issue is with django actually, using a blog/text field as part of
>> a unique key, as outlined in the link I posted before. Once I changed
>> that field to CharField, it was smooth sailing.
>
> As far as I can gather from the Django bug report you linked us, Og, the
> problem is setting unique=True on TextFields.  And when you think about
> how TEXT is implemented in MySQL, it makes total sense.
>
> Using unique=True, MySQL has to build an index for that column so that
> it can easily verify that there are no rows with the same column value.
> TEXT has an unbounded length, so MySQL refuses to build an index without
> a column length as it would cause a massive performance hit and negate
> the efficiency of an index.
>
> In summation, we can't set unique=True on a TextField, so we have two
> choices:
>
>  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).

>  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.

If the suggestion I made above about manually enforcing uniqueness is
not doable, how bad would it be to have the title potentially
duplicated?  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?

> Hopefully this sheds a little more light into the situation for you,
> Sandy.

It does, I hadn't realized what the actual problem was.

Sandy