Thanks Shawn. I guess your suggestion maybe the only thing I can do about it.
But the problem itself has an interesting background:
I developed an web application handling dynamic online conference registrations;
including
a table BusinessSeason to hold the information about the registration specification
(one
record per event) and a table Participation to hold all the registration records. My
app reads
the registration spec and the registration record (the latter only exist for returning
users) to
generate web pages for user to edit/submit the registration data (preferences for
programs or
lodging etc.)
Records in BusinessSeason are for different events/registrations hence very different
in
terms of reg specification. And the future conference spec can be inserted to the
table and
you see why I just cannot have a fixed schema for the registration data. By using xml
or
plist or any kind of generic data storage, I can store the reg data into the
participation table
along with some standard attributes like event_id, submit_time, reg_id etc.
Now for the management reason, I need to get some statistics from the registration data
and that's why I have to query the column that holds the reg data as xml or plist text.
Things were not too bad as I tried for conferences around 500 people without index the
column. But I should make the database perform better whenver I can.
Thanks again for your help. Can your 2-step query can merge into 1?
Also, just out of curiosity, can oracle do such things? I'm kind of far away from
oracle but
not too long ago I learned there is no way that I can store long text and using sql
query
the text in oracle tables.
----- Original Message -----
From: [EMAIL PROTECTED]
To: Elim Qiu
Cc: MySQL mailing List
Sent: Friday, October 08, 2004 8:15 AM
Subject: Re: data with dynamic schema stored in a column as a property list.
Have you considered a combination of Full-text indexing (to quickly locate
a subset of records that may match your criteria) and regular expression
matching (to eliminate the non-matching results from the results of the
full-text search)? I know it's two steps but your "data" is practically
opaque to the database engine. The field names and the values you want to
search for exist as content, not as standalone fields or name/value pairs
of columns.
Without somehow converting your data stream into some kind of relational
structure, I think that you will be quite restricted in your searching
options.
Sorry I couldn't be more helpful,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Elim Qiu" <[EMAIL PROTECTED]> wrote on 10/07/2004 11:14:49 PM:
> Hi, instead of xml, i stored arbitrary data of the form
> (the actual usage of such mechanism is for more fancy stuff,
> say, dynamic configuration, otherwise this is really not necessary)
>
> {
> name = "Fn, Ln"; // string value
> gender = F; // single word string
> interests = (reading,"drive fast"); // array
> children = (
> { lastName = Howe; firstName = Sam; gender = M; dob =
> "1994-10-07 16:59:26"; },
> { lastName = Howe; firstName = Ann; gender = F; dob =
> "1998-01-26 04:09:12"; }
> );
> creditCards = {
> visa = "XXXXXXXXXXX-xxxxx";
> master = "YYYYYYYYYY-yyyy";
> };
> }
>
> This is called plist and the depth of the hierarchy can go arbitrary
> deep (unknown limit). And it can be converted back
> and forth from dictionary object by a framework.
>
> My task is to find out ways of querying a column holds such text
> data? say, find out whether there is certain key or
> whether a key has certain value. I got some solution via regular
> expression feature of MySQL.
>
> The column type that I use is text. My question now is how to make
> the whole thing perform good. In other words,
> for regular expression querying, should I index the column for
> performance? If so, what kind of index should I use?
>
> Thanks a lot.
