ExtractValue Runs Slower via JDBC than via C API?

[Benjamin Schmaus]

Hi,

I've been experimenting with the new XML features of MySQL and I've noticed that queries using ExtractValue take noticeably longer when querying the database through JDBC than when querying via the C API.

Does anyone know why this might be the case? Does the Connector/J driver do any query parsing before sending the query to the server?

Here are the queries I'm testing with:

-- Retrieve XML by filtering on data in the XML itself
select xml from apnews
where ExtractValue(xml, '//entry/updated') >= curdate()
order by ExtractValue(xml, '//entry/updated') desc;

-- Retrivieve XML by filtering on indexed cols
select xml from apNewsWithIndexes
where updated >= curdate() order by updated desc;

And here are the tables I'm working with:

CREATE TABLE IF NOT EXISTS apNews (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
xml MEDIUMTEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS apNewsWithIndexes (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
newsgroup VARCHAR(255) NOT NULL,
updated DATETIME NOT NULL,
xml MEDIUMTEXT NOT NULL,
INDEX(newsgroup), INDEX(updated)
);

Basically, I just want to see if it's feasible performance-wise to execute queries by filtering on text data contained within XML columns, instead of having separate indexed columns for filtering results.

I have test code and data available if anyone is sufficiently interested.

Thanks,
Ben S.



Edited 1 time(s). Last edit at 07/28/2006 12:31PM by Benjamin Schmaus.