On Friday 02 August 2002 12:47 pm, [EMAIL PROTECTED] wrote:
Lars: I think what you are trying to do is pound a square peg into a round
hole...
MySQL really wasn't designed for this sort of useage. Your selects are going
to be brute force searches on unidexed data, which is exactly why they are so
slow...
I would advise you to look into using an XML or XML Enabled RDBMS. There are
a lot of possible considerations here as to what you might want.
Considerations would include:
XML databases generally have NO idea of referential integrity whatsoever, nor
transactions, etc. They also generally use some sort of query language that
is either totally proprietary or based to some degree on XQuery, XPath,
XPointer, etc. Expect the query language to be unfamiliar to SQL users and
non-portable from database to database. On the flip side they can give you
excellent performance for this type of application and have other desirable
features like support for Schemas etc.
XML enabled databases now include most of the major vendor's offerings,
though they often need an add-on module. I'm not really incredibly familiar
with these, not having worked with one closely, but they generally are a
standard RDBMS which has added features to allow mapping of XML to a
relational schema, and even sometimes "ad-hoc" XML support (though generally
its not indexed in that case).
The other option is to keep using MySQL but abadon the idea of storing XML in
a blob. In other words you would have to create a relational mapping of your
XML schema to an RDBMS schema. Then your application would accept XML,
extract the necessary data from it, create appropriate INSERT statements....
For output it would be necessary to reverse the process. If its a fairly
simple application this is not a big deal. If its a complicated application
with complex and changing schema, then you probably would want to find a
commercial tool which supports that sort of thing, there are several good
ones out there that will make it easier to do the mappings.
Hope this is helpful. You might want to check out O'Reilly's www.xml.com web
site, it has a lot of interesting articles discussing this sort of thing.
> Heyho folks,
>
> I hope I am in the correct mailing list here.
>
> We do have a performance problem with mySQL.
>
> In the database we have :
>
> INSERT INTO art_xml VALUES ('0', '<article-info><pn lang="en">english
> product nr 0 </pn><pd lang="en">and went away, and two technicians
> remained with instrument and started to\r and </pd><pn lang="de">deutsches
> product nr 0 </pn><pd lang="de">and protected meaning of civil life.\r and
> </pd></article-info>');
>
> lots of these entries...
>
> and querying happens with :
>
> SELECT pr.*, SUBSTRING(xml_description, LOCATE('', xml_description) +
> length('') ) as jezus FROM art_xml as pr WHERE pr.xml_description regexp
> '<[[:space:]]*pn[[:space:]]*lang="de"[[:space:]]*>[[:alnum:][:space:]]*de[[
>: alnum:][:space:]]*<' ORDER BY jezus
>
> Well this works.. but.. it is f***cking slow... really slow.. serious
> slow...
>
> ( 50,000 entries need much more than 3 minutes to process this select)
>
> So the question is - do we make a major mistake here or is it just so slow
> ?
>
> Thnx folks :)
>
> dodger
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
