3

Problem

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)

My Query

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

Question

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.

  • On your inner select do you really need to return all the columns with *? Or can you do this query with just one column or id? – Shawn E Carter Dec 15 '16 at 3:35
  • Shawn, My understanding is that using SELECT * is often just as quick as actually specifying a column. Since SELECT * only uses pointers. Second Since I'm setting the variable @RowTotal = (Select statement with inner select) I thought that only ran one time. IF that assumption is correct, that SELECT * should have very insignificant difference on performance. Please correct me if I'm wrong. – DarbyM Dec 15 '16 at 4:12
  • 1
    The IF(@RowNum = @RowTotal, ...) seems wrong. One counter (RowNum) goes over the whole table, the other (RowTotal) has counted only the distinct BidID values. – ypercubeᵀᴹ Dec 15 '16 at 9:18
  • Plus you probably need an ORDER BY in the query. See the answer by AndriyM, seems to be in the right direction to solve your issue. – ypercubeᵀᴹ Dec 15 '16 at 9:24
  • 1
    The COUNT(*) does not need the extra layer of SELECT. – Rick James Dec 16 '16 at 17:13
4

It seems to me you just need to use the GROUP_CONCAT aggregate function:

SET @JSON :=
  CONCAT(
    '[',
    (
      SELECT
        GROUP_CONCAT(
          CONCAT(
            '["', ctd.BidID,
            '","', REPLACE(REPLACE(ctd.Description,           '"', '\\"'),"'", "\\'"), 
            '","', REPLACE(REPLACE(ctd.FoundationDescription, '"', '\\"'),"'", "\\'"), 
            '","', REPLACE(REPLACE(ctd.EngDrawingNumber,      '"', '\\"'),"'", "\\'"), 
            '","', REPLACE(REPLACE(ctd.DetailDrawingNumber,   '"', '\\"'),"'", "\\'"),  
            '","', ctd.TakeOffQuantity, 
            '"]'
          )
          ORDER BY <some-column>
        )
      FROM
        `Job1111`.CivilTrackerDetails AS ctd
    ),
    ']'
  )
;

As pointed out by Rick James, applying GROUP_CONCAT to an entire table like that might result in exceeding the default group_concat_max_len value, which is 1024 in MySQL5.7. So, make sure you have set it big enough to accommodate the resulting string by, for instance, setting it session-wise using a SET statement just before the string assignment, like this:

SET group_concat_max_len = 20480;  -- for example
SET @JSON := ...
4

MySQL 5.7

Since it's possible for 5.7 upgrade, you can use GROUP_CONCAT along with JSON_ARRAY which also escapes the inner quotes.

SELECT
  CONCAT(
    '[', 
    GROUP_CONCAT(
      json_array(
        BidID, 
        Description, 
        FoundationDescription, 
        EngDrawingNumber, 
        DetailDrawingNumber, 
        TakeOffQuantity
      )
    ), 
    ']'
  )
FROM
  `Job1111`.CivilTrackerDetails

MySQL >= 8.0.1

JSON_ARRAYAGG

SELECT
  JSON_ARRAYAGG(
    json_array(
      BidID, 
      Description, 
      FoundationDescription, 
      EngDrawingNumber, 
      DetailDrawingNumber, 
      TakeOffQuantity
    )
  )
FROM
  `Job1111`.CivilTrackerDetails
  • This must certainly be superior going forward. – Andriy M Dec 20 '16 at 6:51
  • I agree, the functionality in 8 looks very promising.. but currently my application runs in Google Cloud SQL. and it runs version 5.7. Question, could not the first set of [ ] also be replaced with json_array() in your 5.7 example? After all we are effectively working with an Array of Arrays here. – DarbyM Dec 20 '16 at 19:53
  • 1
    @DarbyM, technically it's an Array of Arrays, but for json_array function it's seen as Array of Strings since the result of GROUP_CONCAT is just a String rather than of json datatype. So if you replace the first set of [] with json_array, you would see that the quotes in array is escaped and also each stringArray is quoted wholy. i.e., ["row1value1", "row1value2"], ["row2value1", "row2value2"] will be produced as ["[\"row1value1\", \"row1value2\"]", "[\"row2value1\", \"row2value2\"]"] instead of [["row1value1", "row1value2"], ["row2value1", "row2value2"]] – The Coder Dec 21 '16 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.