SELECT 
  SQL_CALC_FOUND_ROWS (
    CASE WHEN products.parent_product_id <> 0 THEN products.parent_product_id ELSE products.product_id END
  ) AS product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  MIN(
    IF(
      prices.percentage_discount = 0, 
      prices.price, 
      prices.price - (
        prices.price * prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    products.product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_ids, 
  GROUP_CONCAT(
    products.product_type 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_types, 
  GROUP_CONCAT(
    products.parent_product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS parent_product_ids, 
  products.product_type, 
  products.parent_product_id, 
  descr1.full_description as full_description 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id 
  AND prices_2.lower_limit = 1 
  AND prices_2.price < prices.price 
  AND prices_2.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_categories.storefront_id IN (0, 1) 
WHERE 
  1 
  AND products.product_id NOT IN (21) 
  AND companies.status IN ('A') 
  AND prices.price >= 28.49 
  AND prices.price <= 31.49 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND prices.usergroup_id IN (0, 0, 1) 
  AND prices_2.price IS NULL 
  AND products.product_type != 'D' 
GROUP BY 
  product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 3

Query time 0.00831

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "51.67"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "buffer_result": {
          "using_temporary_table": true,
          "nested_loop": [
            {
              "table": {
                "table_name": "companies",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY"
                ],
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 1,
                "filtered": "20.00",
                "cost_info": {
                  "read_cost": "3.05",
                  "eval_cost": "0.20",
                  "prefix_cost": "3.25",
                  "data_read_per_join": "6K"
                },
                "used_columns": [
                  "company_id",
                  "status",
                  "company"
                ],
                "attached_condition": "(`atulecarter_atul_demo2`.`companies`.`status` = 'A')"
              }
            },
            {
              "table": {
                "table_name": "cscart_categories",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY",
                  "c_status",
                  "p_category_id"
                ],
                "rows_examined_per_scan": 84,
                "rows_produced_per_join": 3,
                "filtered": "4.00",
                "using_join_buffer": "Block Nested Loop",
                "cost_info": {
                  "read_cost": "19.61",
                  "eval_cost": "0.67",
                  "prefix_cost": "23.53",
                  "data_read_per_join": "8K"
                },
                "used_columns": [
                  "category_id",
                  "storefront_id",
                  "usergroup_ids",
                  "status"
                ],
                "attached_condition": "(((`atulecarter_atul_demo2`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo2`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo2`.`cscart_categories`.`usergroup_ids`)) and (`atulecarter_atul_demo2`.`cscart_categories`.`status` in ('A','H')) and (`atulecarter_atul_demo2`.`cscart_categories`.`storefront_id` in (0,1)))"
              }
            },
            {
              "table": {
                "table_name": "products_categories",
                "access_type": "ref",
                "possible_keys": [
                  "PRIMARY",
                  "pt"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "category_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo2.cscart_categories.category_id"
                ],
                "rows_examined_per_scan": 4,
                "rows_produced_per_join": 13,
                "filtered": "99.77",
                "using_index": true,
                "cost_info": {
                  "read_cost": "3.64",
                  "eval_cost": "2.68",
                  "prefix_cost": "29.87",
                  "data_read_per_join": "214"
                },
                "used_columns": [
                  "product_id",
                  "category_id"
                ],
                "attached_condition": "(`atulecarter_atul_demo2`.`products_categories`.`product_id` <> 21)"
              }
            },
            {
              "table": {
                "table_name": "products",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "status"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo2.products_categories.product_id"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 0,
                "filtered": "7.40",
                "cost_info": {
                  "read_cost": "13.41",
                  "eval_cost": "0.20",
                  "prefix_cost": "45.96",
                  "data_read_per_join": "4K"
                },
                "used_columns": [
                  "product_id",
                  "product_type",
                  "status",
                  "company_id",
                  "usergroup_ids",
                  "parent_product_id"
                ],
                "attached_condition": "((`atulecarter_atul_demo2`.`products`.`company_id` = `atulecarter_atul_demo2`.`companies`.`company_id`) and ((`atulecarter_atul_demo2`.`products`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo2`.`products`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo2`.`products`.`usergroup_ids`)) and (`atulecarter_atul_demo2`.`products`.`status` = 'A') and (`atulecarter_atul_demo2`.`products`.`product_type` <> 'D'))"
              }
            },
            {
              "table": {
                "table_name": "prices",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo2.products_categories.product_id"
                ],
                "rows_examined_per_scan": 4,
                "rows_produced_per_join": 0,
                "filtered": "3.28",
                "index_condition": "((`atulecarter_atul_demo2`.`prices`.`lower_limit` = 1) and (`atulecarter_atul_demo2`.`prices`.`usergroup_id` in (0,0,1)))",
                "cost_info": {
                  "read_cost": "4.20",
                  "eval_cost": "0.03",
                  "prefix_cost": "50.95",
                  "data_read_per_join": "3"
                },
                "used_columns": [
                  "product_id",
                  "price",
                  "percentage_discount",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "((`atulecarter_atul_demo2`.`prices`.`price` >= 28.49) and (`atulecarter_atul_demo2`.`prices`.`price` <= 31.49))"
              }
            },
            {
              "table": {
                "table_name": "prices_2",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo2.products_categories.product_id"
                ],
                "rows_examined_per_scan": 4,
                "rows_produced_per_join": 0,
                "filtered": "9.83",
                "not_exists": true,
                "cost_info": {
                  "read_cost": "0.55",
                  "eval_cost": "0.01",
                  "prefix_cost": "51.61",
                  "data_read_per_join": "1"
                },
                "used_columns": [
                  "product_id",
                  "price",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "(<if>(found_match(prices_2), isnull(`atulecarter_atul_demo2`.`prices_2`.`price`), true) and <if>(is_not_null_compl(prices_2), ((`atulecarter_atul_demo2`.`prices_2`.`lower_limit` = 1) and (`atulecarter_atul_demo2`.`prices_2`.`price` < `atulecarter_atul_demo2`.`prices`.`price`) and (`atulecarter_atul_demo2`.`prices_2`.`usergroup_id` in (0,0,1))), true))"
              }
            },
            {
              "table": {
                "table_name": "descr1",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "product_id"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id",
                  "lang_code"
                ],
                "key_length": "9",
                "ref": [
                  "atulecarter_atul_demo2.products_categories.product_id",
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 0,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "0.05",
                  "eval_cost": "0.01",
                  "prefix_cost": "51.67",
                  "data_read_per_join": "239"
                },
                "used_columns": [
                  "product_id",
                  "lang_code",
                  "product",
                  "full_description"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Result

product_id product company_name price product_ids product_types parent_product_ids product_type parent_product_id full_description
199 B.B. King - Original Greatest Hits CD CS-Cart test 29.98000000 199 P 0 P 0 <p> <div class="wiki_section" style="margin-bottom: 15px; line-height: 18px; color: #3b3b3b; font-family: 'Gill Sans MT', tahoma, arial, sans-serif; font-size: 12px; text-align: left; background-color: #ffffff;">Product Details&nbsp;<br /> <ul class="wiki_list"> <li class="wiki_listitem">Audio CD (September 13, 2005)</li> <li class="wiki_listitem">Original Release Date: September 13, 2005</li> <li class="wiki_listitem">Number of Discs: 2</li> <li class="wiki_listitem">Label: Virgin Records Us</li> </ul> </div> <div class="wiki_section" style="margin-bottom: 15px; line-height: 18px; color: #3b3b3b; font-family: 'Gill Sans MT', tahoma, arial, sans-serif; font-size: 12px; text-align: left; background-color: #ffffff;">Disc: 1 <ul class="wiki_list"> <li class="wiki_listitem">1. 3 O'Clock Blues</li> <li class="wiki_listitem">2. Blind Love</li> <li class="wiki_listitem">3. She's Dynamite</li> <li class="wiki_listitem">4. You Know I Love You</li> <li class="wiki_listitem">5. Please Love Me</li> <li class="wiki_listitem">6. Story Fom My Heart And Soul</li> <li class="wiki_listitem">7. The Woman I Love</li> <li class="wiki_listitem">8. Neighborhood Affair</li> <li class="wiki_listitem">9. When My Heart Beats Like A Hammer</li> <li class="wiki_listitem">10. Every Day I Have The Blues</li> <li class="wiki_listitem">11. Love You Baby (Take A Swing With Me)</li> <li class="wiki_listitem">12. Ten Long Years</li> <li class="wiki_listitem">13. You Upset Me, Baby</li> <li class="wiki_listitem">14. Boogie Rock (House Rocker)</li> <li class="wiki_listitem">15. Crying Won't Help You</li> <li class="wiki_listitem">16. Bad Luck</li> <li class="wiki_listitem">17. Did You Ever Love A Woman</li> <li class="wiki_listitem">18. You Don't Know</li> <li class="wiki_listitem">19. Sweet Little Angel</li> <li class="wiki_listitem">20. On My Word Of Honor</li> </ul> <br />Disc: 2 <ul class="wiki_list"> <li class="wiki_listitem">1. Be Careful With A Fool</li> <li class="wiki_listitem">2. Please Accept My Love</li> <li class="wiki_listitem">3. Days Of Old</li> <li class="wiki_listitem">4. Worry, Worry</li> <li class="wiki_listitem">5. Confessin' The Blues</li> <li class="wiki_listitem">6. Sweet Sixteen Parts 1 &amp; 2</li> <li class="wiki_listitem">7. Ain't Nobody's Business</li> <li class="wiki_listitem">8. Walkin' Dr. Bill</li> <li class="wiki_listitem">9. My Own Fault</li> <li class="wiki_listitem">10. You Done Lost Your Good Thing Now</li> <li class="wiki_listitem">11. Downhearted (How Blue Can You Get?)</li> <li class="wiki_listitem">12. I'll Survive</li> <li class="wiki_listitem">13. Mashed Potato Twist</li> <li class="wiki_listitem">14. Down Now</li> <li class="wiki_listitem">15. Peace Of Mind</li> <li class="wiki_listitem">16. That Evil Child</li> <li class="wiki_listitem">17. Blues Stay Away From Me</li> <li class="wiki_listitem">18. Rock Me Baby</li> <li class="wiki_listitem">19. Miss Martha King</li> <li class="wiki_listitem">20. When Your Baby Packs Up And Goes</li> </ul> </div> </p>
11 Collegiate 72 Tee CS-Cart test 30.00000000 11 P 0 P 0 <p>It's not how old you are, it's how stylish, and this men's adidas Originals Collegiate 72 Tee shirt feels just as fresh as it was the day the Trefoil was born in 1972. That's why this t-shirt features a collegiate "72" on the front.</p>
195 Crossroads Guitar Festival 2010 (2DVD) CS-Cart test 29.99000000 195 P 0 P 0 <p> <p>2DVD edition of the 2010 Crossroads Guitar Festival. Featuring: Eric Clapton, Jeff Beck,Sheryl Crow,Vince Gill, Buddy Guy, B.B. King,John Mayer, Bill Murray,Steve Winwood,Ronnie Wood, ZZ Top,and more PLUS behind-the-scenes footage and interviews.</p> <p>&nbsp;</p> <p>DVD 1</p> <p>&nbsp;</p> <p>01 Promise Land - By Sonny Landreth with Eric Clapton</p> <p>02 Z. Rider - By Sonny Landreth with Eric Clapton</p> <p>03 Traveling Shoes - By Robert Randolph &amp; The Family Band</p> <p>04 Going Down - By Pino Daniele, Joe Bonamassa, Robert Randolph &amp; The Family Band</p> <p>05 Killing Floor - By Robert Cray with Jimmie Vaughan &amp; Hubert Sumlin</p> <p>06 Six Strings Down - By Jimmie Vaughan with Robert Cray &amp; Hubert Sumlin</p> <p>07 Waiting For The Bus - By ZZ Top</p> <p>08 Jesus Just Left Chicago - By ZZ Top</p> <p>09 Gypsy Blood - By Doyle Bramhall II</p> <p>10 In My Time Of Dying (Jesus Make Up My Dying Bed) - By Doyle Bramhall II</p> <p>11 Bright Lights - By Gary Clark Jr.</p> <p>12 Long Road Home - By Sheryl Crow with Derek Trucks, Susan Tedeschi, Doyle Bramhall II &amp; Gary Clark Jr.</p> <p>13 Our Love Is Fading - By Sheryl Crow with Eric Clapton, Doyle Bramhall II &amp; Gary Clark Jr.</p> <p>14 Blackwaterside - By Bert Jansch</p> <p>15 Mississippi Blues - By Stefan Grossman &amp; Keb' Mo'</p> <p>16 Roll And Tumble Blues - By Stefan Grossman</p> <p>17 One More Last Chance - By Vince Gill with Keb' Mo', James Burton, Earl Klugh &amp; Albert Lee</p> <p>18 Mystery Train - By Vince Gill with James Burton, Albert Lee, Keb' Mo' &amp; Earl Klugh</p> <p>19 Lay Down Sally - By Vince Gill with Sheryl Crow, Keb' Mo', Albert Lee, James Burton &amp; Earl Klugh</p> <p>20 Angelina - By Earl Klugh</p> <p>21 Vonetta - By Earl Klugh</p> <p>22 Who Did You Think I Was - By John Mayer Trio</p> <p>23 Ain't No Sunshine - By John Mayer Trio</p> <p>&nbsp;</p> <p>DVD 2</p> <p>&nbsp;</p> <p>01 Midnight In Harlem - By Derek Trucks &amp; Susan Tedeschi Band</p> <p>02 Coming Home - By Derek Trucks &amp; Susan Tedeschi Band with Warren Haynes</p> <p>03 Soulshine - By Warren Haynes</p> <p>04 Don't Keep Me Wondering - By David Hidalgo &amp; Cesar Rosas with Derek Trucks</p> <p>05 Space Captain - By Derek Trucks &amp; Susan Tedeschi Band with Warren Haynes, David Hidalgo, Cesar Rosas &amp; Chris Stainton</p> <p>06 Five Long Years - By Buddy Guy with Jonny Lang &amp; Ronnie Wood</p> <p>07 Miss You - By Buddy Guy with Jonny Lang &amp; Ronnie Wood</p> <p>08 Hammerhead - By Jeff Beck</p> <p>09 Nessun Dorma - By Jeff Beck</p> <p>10 Crossroads</p> <p>11 Hands Of The Saints - By Citizen Cope &amp; Eric Clapton</p> <p>12 I Shot The Sheriff</p> <p>13 Shake Your Money Maker - By Eric Clapton &amp; Jeff Beck</p> <p>14 Had To Cry Today - By Steve Winwood &amp; Eric Clapton</p> <p>15 Voodoo Chile - By Eric Clapton &amp; Steve Winwood</p> <p>16 Dear Mr. Fantasy - By Steve Winwood &amp; Eric Clapton</p> <p>17 The Thrill Is Gone - By B.B. King &amp; Ensemble</p> <div></div> </p>