KB Query

gistfile1.txt

SELECT SQL_CALC_FOUND_ROWS wp_570_posts.ID
FROM wp_570_posts
INNER JOIN wp_570_postmeta AS fdi1 ON (wp_570_posts.ID = fdi1.post_id)
INNER JOIN wp_570_postmeta AS fdi2 ON (wp_570_posts.ID = fdi2.post_id)
INNER JOIN wp_570_postmeta AS fdi_stocknumberpostmeta ON wp_570_posts.id = fdi_stocknumberpostmeta.post_id
AND fdi_stocknumberpostmeta.meta_key = 'stock'
WHERE 1=1
  AND (
        wp_570_posts.post_title REGEXP '/\b(new)\b/'
        OR wp_570_posts.post_excerpt REGEXP '/\b(new)\b/'
        OR wp_570_posts.post_content REGEXP '/\b(new)\b/'
      )
  AND (wp_570_posts.post_password = '')
  AND wp_570_posts.post_type = 'vehicle'
  AND (wp_570_posts.post_status = 'publish')
  AND fdi1.meta_key = 'feed_id'
  AND fdi2.meta_key = 'dateinstock'
  OR wp_570_posts.post_name REGEXP '/\b(new)\b/'
  OR fdi_stocknumberpostmeta.meta_value  REGEXP '/\b(new)\b/'
GROUP BY wp_570_posts.ID
ORDER BY CASE
             WHEN (fdi1.meta_value = 'h_northbaynissan') THEN 1
             ELSE 2
         END,
         CAST(fdi2.meta_value AS DECIMAL) ASC
LIMIT 420,20

Below 1 is with removed inner join

SELECT SQL_CALC_FOUND_ROWS wp_570_posts.ID
FROM wp_570_posts
INNER JOIN wp_570_postmeta AS fdi1 ON (wp_570_posts.ID = fdi1.post_id)
INNER JOIN wp_570_postmeta AS fdi_stocknumberpostmeta ON wp_570_posts.id = fdi_stocknumberpostmeta.post_id
AND fdi_stocknumberpostmeta.meta_key = 'stock'
WHERE 1=1
  AND (
        wp_570_posts.post_title REGEXP '[[:<:]]new[[:>:]]'
        OR wp_570_posts.post_excerpt REGEXP '[[:<:]]new[[:>:]]'
        OR wp_570_posts.post_content REGEXP '[[:<:]]new[[:>:]]'
      )
  AND wp_570_posts.post_password = ''
  AND wp_570_posts.post_type = 'vehicle'
  AND wp_570_posts.post_status = 'publish'
  AND fdi1.meta_key IN('feed_id','dateinstock')
  OR wp_570_posts.post_name REGEXP '[[:<:]]new[[:>:]]'
  OR fdi_stocknumberpostmeta.meta_value  REGEXP '[[:<:]]new[[:>:]]'
GROUP BY wp_570_posts.ID
ORDER BY CASE
             WHEN (fdi1.meta_key='feed_id' AND fdi1.meta_value = 'h_northbaynissan') THEN 1
             ELSE 2
         END,
         IF(fdi1.meta_key='dateinstock', CAST(fdi1.meta_value AS DECIMAL), 9999999999) ASC
LIMIT 420,20

FROM wp_570_posts
INNER JOIN wp_570_postmeta AS fdi1 ON (wp_570_posts.ID = fdi1.post_id)
INNER JOIN wp_570_postmeta AS fdi2 ON (wp_570_posts.ID = fdi2.post_id)
INNER JOIN wp_570_postmeta AS fdi_stocknumberpostmeta ON wp_570_posts.id = fdi_stocknumberpostmeta.post_id
AND fdi_stocknumberpostmeta.meta_key = 'stock'
WHERE 1=1
  AND (
        wp_570_posts.post_title REGEXP '[[:<:]]new[[:>:]]'
        OR wp_570_posts.post_excerpt REGEXP '[[:<:]]new[[:>:]]'
        OR wp_570_posts.post_content REGEXP '[[:<:]]new[[:>:]]'
      )
  AND (wp_570_posts.post_password = '')
  AND wp_570_posts.post_type = 'vehicle'
  AND (wp_570_posts.post_status = 'publish')
  AND fdi1.meta_key = 'feed_id'
  AND fdi2.meta_key = 'dateinstock'
  OR wp_570_posts.post_name REGEXP '[[:<:]]new[[:>:]]'
  OR fdi_stocknumberpostmeta.meta_value  REGEXP '[[:<:]]new[[:>:]]'
GROUP BY wp_570_posts.ID
ORDER BY CASE
             WHEN (fdi1.meta_value = 'h_northbaynissan') THEN 1
             ELSE 2
         END,
         CAST(fdi2.meta_value AS DECIMAL) ASC
LIMIT 420,20

SELECT SQL_CALC_FOUND_ROWS wp_577_posts.ID FROM wp_577_posts INNER JOIN wp_577_postmeta AS fdi1 ON (wp_577_posts.ID = fdi1.post_id) INNER JOIN wp_577_postmeta AS fdi2 ON (wp_577_posts.ID = fdi2.post_id) INNER JOIN wp_577_postmeta AS fdi_stocknumberpostmeta ON wp_577_posts.id = fdi_stocknumberpostmeta.post_id AND fdi_stocknumberpostmeta.meta_key = 'stock' WHERE 1=1 AND ((((wp_577_posts.post_title LIKE '% 324') OR (wp_577_posts.post_title LIKE '324 %') OR (wp_577_posts.post_title LIKE '% 324 %')) OR ((wp_577_posts.post_excerpt LIKE '% 324') OR (wp_577_posts.post_excerpt LIKE '324 %') OR (wp_577_posts.post_excerpt LIKE '% 324 %')) OR ((wp_577_posts.post_content LIKE '% 324') OR (wp_577_posts.post_content LIKE '324 %') OR (wp_577_posts.post_content LIKE '% 324 %')))) AND (wp_577_posts.post_password = '') AND wp_577_posts.post_type = 'vehicle' AND (wp_577_posts.post_status = 'publish') AND fdi1.meta_key = 'feed_id' AND fdi2.meta_key = 'dateinstock' OR ( (wp_577_posts.post_name LIKE '%324') OR (wp_577_posts.post_name LIKE '%324%') OR (wp_577_posts.post_name LIKE '324%') ) OR ( ( fdi_stocknumberpostmeta.meta_value LIKE '%324' OR fdi_stocknumberpostmeta.meta_value LIKE '%324%' OR fdi_stocknumberpostmeta.meta_value LIKE '324%' ) ) GROUP BY wp_577_posts.ID ORDER BY CASE WHEN (fdi1.meta_value = 'h_goldenstatenissan') THEN 1 ELSE 2 END,CAST(fdi2.meta_value AS DECIMAL) ASC LIMIT 0, 20;

SELECT SQL_CALC_FOUND_ROWS wp_577_posts.ID
FROM wp_577_posts
INNER JOIN wp_577_postmeta AS fdi1 ON (wp_577_posts.ID = fdi1.post_id)
INNER JOIN wp_577_postmeta AS fdi_stocknumberpostmeta ON wp_577_posts.id = fdi_stocknumberpostmeta.post_id
AND fdi_stocknumberpostmeta.meta_key = 'stock'
WHERE 1=1
  AND (
        wp_577_posts.post_title REGEXP '[[:<:]]324[[:>:]]'
        OR wp_577_posts.post_excerpt REGEXP '[[:<:]]324[[:>:]]'
        OR wp_577_posts.post_content REGEXP '[[:<:]]324[[:>:]]'
      )
  AND wp_577_posts.post_password = ''
  AND wp_577_posts.post_type = 'vehicle'
  AND wp_577_posts.post_status = 'publish'
  AND fdi1.meta_key IN('feed_id','dateinstock')
  OR wp_577_posts.post_name REGEXP '[[:<:]]324[[:>:]]'
  OR fdi_stocknumberpostmeta.meta_value  REGEXP '[[:<:]]324[[:>:]]'
GROUP BY wp_577_posts.ID
ORDER BY CASE
             WHEN (fdi1.meta_key='feed_id' AND fdi1.meta_value = 'h_goldenstatenissan') THEN 1
             ELSE 2
         END,
         IF(fdi1.meta_key='dateinstock', CAST(fdi1.meta_value AS DECIMAL), 9999999999) ASC
LIMIT 0,20

SELECT
   SQL_CALC_FOUND_ROWS wp_577_posts.ID 
FROM
   wp_577_posts 
   INNER JOIN
      wp_577_postmeta AS fdi1 
      ON (wp_577_posts.ID = fdi1.post_id) 
   INNER JOIN
      wp_577_postmeta AS fdi2 
      ON (wp_577_posts.ID = fdi2.post_id) 
   INNER JOIN
      wp_577_postmeta AS fdi_stocknumberpostmeta 
      ON wp_577_posts.id = fdi_stocknumberpostmeta.post_id 
      AND fdi_stocknumberpostmeta.meta_key = 'stock' 
WHERE
   1 = 1 
   AND (
        wp_577_posts.post_title REGEXP '[[:<:]]324[[:>:]]'
        OR wp_577_posts.post_excerpt REGEXP '[[:<:]]324[[:>:]]'
        OR wp_577_posts.post_content REGEXP '[[:<:]]324[[:>:]]'
      )
   AND 
   (
      wp_577_posts.post_password = ''
   )
   AND wp_577_posts.post_type = 'vehicle' 
   AND 
   (
      wp_577_posts.post_status = 'publish'
   )
   AND fdi1.meta_key = 'feed_id' 
   AND fdi2.meta_key = 'dateinstock' 
   OR 
   (
      (wp_577_posts.post_name LIKE '%324') 
      OR 
      (
         wp_577_posts.post_name LIKE '%324%'
      )
      OR 
      (
         wp_577_posts.post_name LIKE '324%'
      )
   )
   OR 
   (
      ( fdi_stocknumberpostmeta.meta_value LIKE '%324' 
      OR fdi_stocknumberpostmeta.meta_value LIKE '%324%' 
      OR fdi_stocknumberpostmeta.meta_value LIKE '324%' ) 
   )
GROUP BY
   wp_577_posts.ID 
ORDER BY
   CASE
      WHEN
         (
            fdi1.meta_value = 'h_goldenstatenissan'
         )
      THEN
         1 
      ELSE
         2 
   END
, CAST(fdi2.meta_value AS DECIMAL) ASC LIMIT 0, 20