Custom fields schema

[Sean McAfee]

Myk Melez <myk at mozilla.org> wrote:
>Sean McAfee wrote:
>>Is this enough data to put this issue to rest?  Pretty please?

>Not really.  First, it doesn't measure the impact of indexes.

Which indexes would those be?

>Second, 
>it doesn't include the cost of looking up field IDs.

Insignificant, even in the ludicrous case of hundreds of thousands of
fields, thanks to indices.

>Third, it only 
>measures search performance, but most queries retrieve bug data for 
>specific bugs.

The search yields bug IDs, which are looked up by primary key in all data
tables.  Again, insignificant.

>Fourth, it doesn't take what bbaetz says into account.  

My revised tests took most of what he said into account.  What did I miss
that's important?

(By the way, I was unaware of the "one index per table per query" MySQL
limitation bbaetz mentioned.  I was prepared to see my solution's
performance dive into the ground with larger numbers of fields of different
types, but it didn't happen.  What's the deal?  If it's true, I guess "one
index" is counted even if a table is joined against multiple times.  All of
my query joins use only the index on field_id.)

>And fifth, I never claimed bugs table columns were more performant, only 
>that they were performant enough, which they are, considering the 
>"custom fields" already in use as standard colums.

13-14 seconds versus 4-5 seconds is "performant enough"?

It's not as if there's even a greater cost associated with developing the
faster solution.  It exists today.  The slower one does not.

>But the primary reason it doesn't put the issue to rest is that sparse 
>fields go into separate tables under the fields-as-columns proposal as 
>well, so that proposal gets any benefit to separate tables.

Do you have a specific proposal that can be tested?  And, er, I don't want
to sound rude, but would you mind testing it yourself?  I've already done
quite a lot in that area.

As an aside, my own intuition is against the notion of modifying the
database schema as part of routine system administration.


--Sean