trying to combine two nested queries for efficiency

2 comments

1. You can use your query as a column also but make sure your subquery should return one record for safe side i have added LIMIT 1 in the subquery or if there are morethan 1 result you can remove the limit and can use GROUP_CONCAT(meta_value) but baware it has a default limit of 1024 characters

   SELECT wpm.post_id,
          MAX(CASE WHEN wpm.meta_key = 'size' THEN wpm.meta_value END) size,
          MAX(CASE WHEN wpm.meta_key = 'adlink' THEN wpm.meta_value END) adlink,
          MAX(CASE WHEN wpm.meta_key = 'frontpage' THEN wpm.meta_value END) frontpage,
          MAX(CASE WHEN wpm.meta_key = 'expiration' THEN wpm.meta_value END) expiration,
          MAX(CASE WHEN wpm.meta_key = 'image1' THEN wpm.meta_value END) image1,
          MAX(CASE WHEN wpm.meta_key = 'image2' THEN wpm.meta_value END) image2,
          MAX(CASE WHEN wpm.meta_key = 'image3' THEN wpm.meta_value END) image3,
          MAX(CASE WHEN wpm.meta_key = 'image4' THEN wpm.meta_value END) image4,
          MAX(CASE WHEN wpm.meta_key = 'iframe' THEN wpm.meta_value END) iframe,
          MAX(CASE WHEN wpm.meta_key = 'location' THEN wpm.meta_value END) location,
          MAX(CASE WHEN wpm.meta_key = 'sublocation' THEN wpm.meta_value END) sublocation,
   (
   SELECT 
     meta_value 
   FROM
     wp_postmeta 
   WHERE post_id IN 
     (SELECT 
       meta_value 
     FROM
       wp_postmeta 
     WHERE post_id = wpm. post_id
       AND meta_key LIKE 'image%')
   LIMIT 1) images       
   
       FROM wp_postmeta wpm WHERE wpm.post_id IN 
         (SELECT post_id FROM wp_postmeta 
          WHERE meta_value LIKE 'Run Of Site') GROUP BY wpm.post_id
   

   For the group_concat()

   (
   SELECT 
    GROUP_CONCAT(meta_value)
   FROM
     wp_postmeta 
   WHERE post_id IN 
     (SELECT 
       meta_value 
     FROM
       wp_postmeta 
     WHERE post_id = wpm. post_id
       AND meta_key LIKE 'image%')
   ) images  
   

   Log in to Reply

2. To improve efficiency, replace the IN (subquery) with equivalent join operations, and ensure that you have suitable indexes in place.

   In the first query, replace this:

       FROM wp_postmeta WHERE post_id in 
         (SELECT post_id from wp_postmeta 
          WHERE meta_value LIKE 'Run Of Site') GROUP BY Post_id
   

   with this:

      FROM wp_postmeta m
      JOIN ( SELECT r.post_id 
               FROM wp_postmeta r
              WHERE r.meta_value LIKE 'Run Of Site'
             GROUP BY r.post_id
           ) q
        ON q.post_id = m.post_id 
     GROUP BY m.post_id
   

   AND change the post_id reference in the SELECT list to m.post_id (to avoid an “ambiguous column” reference error.)

   For the second query, replace the “IN (subquery)” with a JOIN operation:

   SELECT t.meta_value
     FROM wp_postmeta t
     JOIN ( SELECT q.meta_value
              FROM wp_postmeta q 
             WHERE q,meta_key LIKE 'image%'
               AND q.post_id = " . $row{'post_id'} ."
          ) s
       ON s.meta_value = t.meta_value
    ORDER BY t.meta_value
   

   ---

   Combining the two queries is problematic because the second query can return more than one row that matches on post_id.

   To get result set returned, we need to define what that result set is going to look like.

   There’s three general approaches. Briefly,

   One way is to “repeat” the values returned by the first query, but that doesn’t look like a good fit with your current code. The “fetch next” would need to check if the post_id had already been processed, and ignore the duplicate values. (This is the approach used by several ORM frameworks to improve efficiency; those have a code layer that strips out the duplicates, and creates the distinct objects.)

   Another way is to return a fixed number of values from the second query, each as a column in the second. Note that the SQL SELECT statement has to define the number of columns to return and the datatype of each. That can’t be changed dynamically when the query runs. If we can specify a finite upper bound of the number of rows to return, then we can craft SQL text to accomplish this, though the resulting statement can be a bit daunting, and there are some performance concerns.

   The most convenient way to return a “set” of values is to return them as a single string, with delimiters. The MySQL GROUP_CONCAT function is a very handy way of doing that, with some limitations. First, we have to have a delimiter between the values; the default delimiter is a comma, but any character can be used (the “pipe” character seems to be pretty popular… to avoid ambiguity, we have to guarantee that the delimiter character does not appear in the values being concatenated together. The other limit is the size of the returned by the function; the maximum size is limited by the MySQL max_allowed_packet variable.

   ---

   Log in to Reply