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.
