Thanks for the history. It's always good to learn how these things come into existence. There are some storage schemes for hierarchical information that you may be able to apply to your needs.
Here is a good tutorial various methods: http://www.sitepoint.com/article/hierarchical-data-database (esp: the modified preorder method) For performance reasons, I would not try to combine both queries into one. Because you will be using regular expressions to locate substrings within your stored data, indexes will be of no use to you for that portion of the query. Preselecting those records that match a full-text index (maybe by storing them in a temporary table) will greatly reduce the number of records you will have to scan with your regular expressions. This way, at least part of your query happens with an index assisting it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Elim Qiu" <[EMAIL PROTECTED]> wrote on 10/08/2004 01:41:33 PM: > 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.
