Bug #28834 Frq: New "type" of indexes on XML documents is needed
Submitted: 1 Jun 2007 12:27 Modified: 1 Jun 2007 12:27
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XML functions Severity:S4 (Feature request)
Version:5.1.x OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[1 Jun 2007 12:27] Valeriy Kravchuk
Description:
On behalf of customer:

"I know youre working with Xpath in 5.1, but I though it would be more
useful if you could work effectively with xml, using indexes like this;

create table mydata (
doc XML,
date DATE,
number INT,
INDEX( doc('/entry/name') );
INDEX( doc('/entry/value') );
INDEX( doc('/entry/name/type') );
);

would speed up queries like

SELECT date,extractValue(doc,'/entry/value') AS value
FROM mydata
WHERE
extractValue(doc,'/entry/name')='hello' AND
extractValue(doc,'/entry/value/type') IN ('one','two','three');

.. also the XML would be stored in some preparsed internal structure so 'extractValue' would not require to parse the xml repeatedly..."

How to repeat:
This is a request for new feature.
[4 Jun 2007 8:10] Hans-Henrik Stærfeldt
Perhaps what is also needed is a way of treating the xml fields as tables/columns.

F.inst.

XML:
<entry>
 <name type=1>Foo</name>
 <name type=2>Bar</name>
 <name type=3>Baz</name>
 <values>
  <property id=1 value=A/>
  <property id=2 value=B/>
  <property id=3 value=C/>
  <property id=4 value=D/>
 </values>
</entry>
<entry>
 <name type=1>Foo</name>
 <name type=2>Bar</name>
 <name type=3>Baz</name>
 <values>
  <property id=4 value=A/>
  <property id=5 value=B/>
  <property id=6 value=C/>
  <property id=7 value=D/>
 </values>
</entry>

SELECT mydata.date, v.'value',n 
FROM 
  mydata.XML.'/entry/name' n,
  mydata.XML.'/entry/value' v,
WHERE
 n.'type'=v.'id' AND
 n.'parent::/parent::'=n.'parent::/parent::/parent::'

(Notice 'n' is both a table and a column, as it
has both content and attributes. Also notice that
you could then check for the the XML being the same,
although, here a nifty 'root::' extension might also
be valuable. Also notice that selected XML elements 
may be accessed to get their context (attributes 
parent etc. I also use a shorthand instead of 'extractValue'
(an xpath string after the element)

Im unsure, but perhaps an XSD could be given in the table
declaration (somehow?), so that the parsing and indexing 
of the XML fields becomes more effective (not always as 
strings), and any update could be tested against the XSD.
[23 Oct 2014 11:28] Daniël van Eeden
The computed virtual columns WL#411 could help here. It is in the 5.7 labs release as presented during OOW 2014. It is also known as 'generated columns'

https://oracleus.activeevents.com/2014/connect/sessionDetail.ww?SESSION_ID=2314 (contains a link to the slides)

http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/