An issue with mysql_unbuffered_query, CONCAT and Wordpress

MySQL Problems related to mysql_unbuffered_query

I have been doing a lot of work with Wordpress lately, mainly developing a number of plugins I have ended up creating to overcome issues with performance or lack of features in existing plugins. One of the plugins I have been working on lately is an XML feed plugin in which I have tried to make use of the database a lot more than other plugins seem to want to.

However for some time I have been experiencing an issue with one of the MySQL interface functions mysql_unbuffered_query. This function is designed to speed up the retrieval of results as records are returned to the client as soon as they are ready rather than waiting for the whole recordset to be completed.

Whilst this seems straight forward I have come across an issue which seems to be directly linked to using this method which affects queries that engage in certain replacement behaviour. In my case I am using a SELECT statement to CONCAT all the necessary column per row into one XML string. Rather than return each individual column by itself and then use PHP to string build and call other database related functions I am trying to save time and database calls by doing as much as possible in one statement. A cut down example of this SQL can be seen below in which I join a number of columns together as well as inserting the relevant value (in this case the tag slug) into the URL.

SELECT CONCAT(' ',REPLACE('http://www.mysite.com/tag/%tag%/','%tag%',t.slug),' ',REPLACE(NOW(),' ','T'),'Z always 1.0 ') as XML

FROM wp_terms AS t

JOIN wp_term_taxonomy AS tt

ON t.term_id = tt.term_id

WHERE tt.taxonomy IN ('post_tag')

ORDER BY Name;

Nothing too complex about that at all. The URL string containing the placeholder is a permalink structure that is obtained before hand and one that can contain multiple placeholders and sections. For the sake of clarity I have kept it simple so it only makes one replacement.

When I run this query in Navicat, from the website with the Wordpress SQL functions or the standard mysql_query functions it runs correctly returning all the rows with the appropriate tag values inserted into the correct %tag% place-holders within the XML e.g

<url><loc>http://www.mysite.com/tag/Sales/</loc><lastmod>2010-09-08T00:37:25Z</lastmod><changefreq>always</changefreq> <priority>1.0</priority></url>
<url><loc>http://www.hottospot.com/tag/Spain/</loc><lastmod>2010-09-08T00:37:25Z</lastmod><changefreq>always</changefreq> <priority>1.0</priority></url>
<url><loc>http://www.hottospot.com/tag/2020/</loc><lastmod>2010-09-08T00:37:25Z</lastmod><changefreq>always</changefreq> <priority>1.0</priority></url>

However when I use mysql_unbuffered_query to run this I get the problem that all rows contain the same data e.g

<url><loc>http://www.mysite.com/tag/Sales/</loc><lastmod>2010-09-08T00:37:25Z</lastmod><changefreq>always</changefreq> <priority>1.0</priority></url>
<url><loc>http://www.hottospot.com/tag/Sales/</loc><lastmod>2010-09-08T00:37:25Z</lastmod><changefreq>always</changefreq> <priority>1.0</priority></url>
<url><loc>http://www.hottospot.com/tag/Sales/</loc><lastmod>2010-09-08T00:37:25Z</lastmod><changefreq>always</changefreq> <priority>1.0</priority></url>

Even if I break the query down to something simple like this REPLACE without the CONCAT it still "misbehaves".

SELECT t.slug,REPLACE('http://www.hottospot.com/tag/%tag%/','%tag%',t.slug)
FROM wp_terms AS t
JOIN wp_term_taxonomy AS tt ON
 t.term_id = tt.term_id
WHERE tt.taxonomy IN ('post_tag')
ORDER BY Name;

and the results will show the same value in the 2nd column for all rows e.g

Col 1	Col 2
Sales	http://www.mysite.com/tag/Sales
Spain	http://www.mysite.com/tag/Sales
2012	http://www.mysite.com/tag/Sales

It definitely seems to be connected to the execution of mysql_unbuffered_query as I can have Navicat open on my office PC connected to my remote server and all these queries run correctly but as soon as on my laptop at home I run the mysql_unbuffered_query query through the website to build the XML everything goes tits up. If I hit the refresh button on the query open in Navicat on my office PC which had been returning the correct results they then come back like I have described with all the values for rows 2 onwards displaying the value from the first row.

This is strange behaviour as I am providing a link identifier parameter when opening the connection and executing the SQL for the mysql_unbuffered_query and I presumed rightly or wrongly that this should have prevented issues like this.

I am yet to find a definitive answer to this problem so if anybody knows please contact me with details. From what it looks like the MySQL query engine has not finished processing the rows correctly when it starts to return them to the client. If the Replacement of values and placeholders used in the REPLACE function wasn't being carried out until the whole recordset was completed rather than after individual rows then this "might" explain it. As I am not an expert on the inner workings of MySQL I cannot say at this point in time however a quick solution to speed up some SQL has become more problematic than I would have though it could.

Any information regarding this issue would be great.