Custom fields schema
etzwane at schwag.org
Fri Dec 24 03:52:05 UTC 2004
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?
>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
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
(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