Custom fields schema

Sean McAfee etzwane at
Fri Dec 24 03:52:05 UTC 2004

Myk Melez <myk at> 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?

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


More information about the developers mailing list