Below is a query I put together in MySQL Workbench that I thought would be relatively simple. But when running it, on a larger data set (7000+ rows), I find it taking over 200 seconds, uses 1GB of RAM, and downloading data from the MySQL server at a rate of 10+ MB/s that entire time. There isn't 100KB worth of data in the entire table. So clearly I'm creating some kind of god awful loop here somehow. But I'm not seeing it.
What I'm doing
Effectively what this query is doing is taking the specified columns from each row, escaping single and double quotes, then contacting them together into a JSON format exactly as needed for a 3rd party API. After this part of the query is done, I then select the last row, and return it. (Not seen here it inserts this data into a "cache" table for quick access from the API)
set @RowNum := 0; set @JSON := ''; set @RowTotal := (SELECT count(*) FROM(SELECT * FROM `Job1111`.CivilTrackerDetails GROUP BY BidID) tb); SELECT (@RowNum := @RowNum + 1) AS ID, (@JSON := CONCAT(@JSON, CONCAT( IF(@RowNum = 1, '[', ''), '["', ctd.BidID, '","', REPLACE(REPLACE(ctd.Description, '"', '\\"'),"'", "\\'"), '","', REPLACE(REPLACE(ctd.FoundationDescription, '"', '\\"'),"'", "\\'"), '","', REPLACE(REPLACE(ctd.EngDrawingNumber, '"', '\\"'),"'", "\\'"), '","', REPLACE(REPLACE(ctd.DetailDrawingNumber, '"', '\\"'),"'", "\\'"), '","', ctd.TakeOffQuantity, '"]', IF(@RowNum = @RowTotal, ']', ',')))) AS JsonData FROM `Job1111`.CivilTrackerDetails ctd
Can you see why this is looping so badly? Do you know a better way to accomplish this task. Preferably within the given methodology of arranging the data within MySQL vs out of MySQL.
The explain shows nothing much.. just a derived ctd from a full table scan. (to be expected).
I'm using version 5.6.3. I have often wondered if some of the JSON compatibility that are supposed to be available in 5.7 may help with this scenario. I have not looked into what is all available in 5.7 yet though. I would be interested in input on that as well.
Ultimately the solution to this issue will be implemented in a stored procedure.