MySQL Bugs: #28834: Frq: New "type" of indexes on XML documents is needed

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

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.

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.

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/