SELECT 
  products.*, 
  product_descriptions.*, 
  MIN(
    IF(
      product_prices.percentage_discount = 0, 
      product_prices.price, 
      product_prices.price - (
        product_prices.price * product_prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    CASE WHEN (
      products_categories.link_type = 'M'
    ) THEN CONCAT(
      products_categories.category_id, 
      'M'
    ) ELSE products_categories.category_id END 
    ORDER BY 
      categories.storefront_id IN (0, 1) DESC, 
      (
        products_categories.link_type = 'M'
      ) DESC, 
      products_categories.category_position ASC, 
      products_categories.category_id ASC
  ) as category_ids, 
  popularity.total as popularity, 
  company_descr.i18n_company as company_name, 
  cd.terms, 
  seo_names.name as seo_name, 
  seo_names.path as seo_path, 
  company_descriptions.company_description, 
  discussion.type as discussion_type 
FROM 
  products 
  LEFT JOIN product_prices ON product_prices.product_id = products.product_id 
  AND product_prices.lower_limit = 1 
  AND product_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN product_descriptions ON product_descriptions.product_id = products.product_id 
  AND product_descriptions.lang_code = 'en' 
  LEFT JOIN company_descriptions as company_descr ON company_descr.company_id = products.company_id 
  AND company_descr.lang_code = 'en' 
  LEFT JOIN companies as companies ON companies.company_id = products.company_id 
  INNER JOIN products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN categories ON categories.category_id = products_categories.category_id 
  AND categories.storefront_id IN (0, 1) 
  AND (
    categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, categories.usergroup_ids) 
    OR FIND_IN_SET(1, categories.usergroup_ids)
  ) 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND categories.status IN ('A', 'H') 
  AND products.status IN ('A', 'H') 
  LEFT JOIN product_popularity as popularity ON popularity.product_id = products.product_id 
  LEFT JOIN company_descriptions AS cd ON cd.company_id = products.company_id 
  AND cd.lang_code = 'en' 
  LEFT JOIN seo_names ON seo_names.object_id = 145301 
  AND seo_names.type = 'p' 
  AND seo_names.dispatch = '' 
  AND seo_names.lang_code = 'en' 
  LEFT JOIN company_descriptions AS company_descriptions ON company_descriptions.company_id = products.company_id 
  AND company_descriptions.lang_code = 'en' 
  LEFT JOIN discussion ON discussion.object_id = products.product_id 
  AND discussion.object_type = 'P' 
WHERE 
  products.product_id = 145301 
  AND (
    companies.status IN ('A') 
    OR products.company_id = 0
  ) 
GROUP BY 
  products.product_id

Query time 0.00266

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4.50"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "products",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY",
              "status",
              "idx_company_id",
              "idx_usergroup_ids"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "product_id",
              "product_code",
              "product_type",
              "status",
              "company_id",
              "list_price",
              "amount",
              "weight",
              "length",
              "width",
              "height",
              "shipping_freight",
              "low_avail_limit",
              "timestamp",
              "updated_timestamp",
              "usergroup_ids",
              "is_edp",
              "edp_shipping",
              "unlimited_download",
              "tracking",
              "free_shipping",
              "zero_price_action",
              "is_pbp",
              "is_op",
              "is_oper",
              "is_returnable",
              "return_period",
              "avail_since",
              "out_of_stock_actions",
              "localization",
              "min_qty",
              "max_qty",
              "qty_step",
              "list_qty_count",
              "tax_ids",
              "age_verification",
              "age_limit",
              "options_type",
              "exceptions_type",
              "details_layout",
              "shipping_params",
              "weight_lbs",
              "weight_oz",
              "product_tax_code",
              "deleted_at",
              "archived_at",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "popularity",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY",
              "total"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "32"
            },
            "used_columns": [
              "product_id",
              "total"
            ]
          }
        },
        {
          "table": {
            "table_name": "cd",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "company_id",
              "lang_code"
            ],
            "key_length": "10",
            "ref": [
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "company_id",
              "lang_code",
              "terms"
            ]
          }
        },
        {
          "table": {
            "table_name": "product_prices",
            "access_type": "ref",
            "possible_keys": [
              "usergroup",
              "product_id",
              "lower_limit",
              "usergroup_id"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.31",
              "eval_cost": "0.10",
              "prefix_cost": "0.41",
              "data_read_per_join": "24"
            },
            "used_columns": [
              "product_id",
              "price",
              "percentage_discount",
              "lower_limit",
              "usergroup_id"
            ],
            "attached_condition": "<if>(is_not_null_compl(product_prices), ((`goimagine`.`product_prices`.`lower_limit` = 1) and (`goimagine`.`product_prices`.`usergroup_id` in (0,0,1))), true)"
          }
        },
        {
          "table": {
            "table_name": "product_descriptions",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY",
              "product_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "product_id",
              "lang_code"
            ],
            "key_length": "11",
            "ref": [
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.62",
              "eval_cost": "0.10",
              "prefix_cost": "1.13",
              "data_read_per_join": "5K"
            },
            "used_columns": [
              "product_id",
              "lang_code",
              "product",
              "shortname",
              "short_description",
              "full_description",
              "meta_keywords",
              "meta_description",
              "search_words",
              "page_title",
              "age_warning_message",
              "promo_text",
              "cls_stop_words",
              "how_its_made",
              "custom_header"
            ]
          }
        },
        {
          "table": {
            "table_name": "company_descr",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "company_id",
              "lang_code"
            ],
            "key_length": "10",
            "ref": [
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.89",
              "eval_cost": "0.10",
              "prefix_cost": "2.13",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "company_id",
              "lang_code",
              "i18n_company"
            ]
          }
        },
        {
          "table": {
            "table_name": "companies",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "company_id"
            ],
            "key_length": "4",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.68",
              "eval_cost": "0.10",
              "prefix_cost": "2.91",
              "data_read_per_join": "14K"
            },
            "used_columns": [
              "company_id",
              "status"
            ],
            "attached_condition": "<if>(found_match(companies), ((`goimagine`.`companies`.`status` = 'A') or false), true)"
          }
        },
        {
          "table": {
            "table_name": "products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 2,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.50",
              "eval_cost": "0.20",
              "prefix_cost": "3.61",
              "data_read_per_join": "32"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type",
              "category_position"
            ]
          }
        },
        {
          "table": {
            "table_name": "categories",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "goimagine.products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "0.50",
              "eval_cost": "0.01",
              "prefix_cost": "4.31",
              "data_read_per_join": "268"
            },
            "used_columns": [
              "category_id",
              "usergroup_ids",
              "status",
              "storefront_id"
            ],
            "attached_condition": "((`goimagine`.`categories`.`storefront_id` in (0,1)) and ((`goimagine`.`categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`goimagine`.`categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`goimagine`.`categories`.`usergroup_ids`))) and (`goimagine`.`categories`.`status` in ('A','H')))"
          }
        },
        {
          "table": {
            "table_name": "seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "const",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.09",
              "eval_cost": "0.01",
              "prefix_cost": "4.40",
              "data_read_per_join": "173"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        },
        {
          "table": {
            "table_name": "company_descriptions",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "company_id",
              "lang_code"
            ],
            "key_length": "10",
            "ref": [
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.04",
              "eval_cost": "0.01",
              "prefix_cost": "4.46",
              "data_read_per_join": "177"
            },
            "used_columns": [
              "company_id",
              "lang_code",
              "company_description"
            ]
          }
        },
        {
          "table": {
            "table_name": "discussion",
            "access_type": "const",
            "possible_keys": [
              "object_id"
            ],
            "key": "object_id",
            "used_key_parts": [
              "object_id",
              "object_type"
            ],
            "key_length": "6",
            "ref": [
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.03",
              "eval_cost": "0.01",
              "prefix_cost": "4.50",
              "data_read_per_join": "2"
            },
            "used_columns": [
              "object_id",
              "object_type",
              "type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id product_code product_type status company_id list_price amount weight length width height shipping_freight low_avail_limit timestamp updated_timestamp usergroup_ids is_edp edp_shipping unlimited_download tracking free_shipping zero_price_action is_pbp is_op is_oper is_returnable return_period avail_since out_of_stock_actions localization min_qty max_qty qty_step list_qty_count tax_ids age_verification age_limit options_type exceptions_type details_layout shipping_params weight_lbs weight_oz product_tax_code deleted_at archived_at position lang_code product shortname short_description full_description meta_keywords meta_description search_words page_title age_warning_message promo_text cls_stop_words how_its_made custom_header price category_ids popularity company_name terms seo_name seo_path company_description discussion_type
145301 GI-145301-JSU P A 1056 0.00 4 0.050 0 0 0 0.00 0 1634849910 1639792226 0 N N N B N R N N N Y 10 0 N 0 0 0 0 N 0 P F a:5:{s:16:"min_items_in_box";i:0;s:16:"max_items_in_box";i:0;s:10:"box_length";i:0;s:9:"box_width";i:0;s:10:"box_height";i:0;} 0.000 0.800 2147483647 en Santa's Milk and Cookie Tray <p>What a great way to start a family tradition.&nbsp; This bamboo "tray" is sandblast engraved and painted so it will last for years.&nbsp; The colors won't fade and the letter will not fall off.</p> <p>The actual dimensions are 6" x 8.5" so will easily store in your Christmas decoration box to be brought out year after year.&nbsp;&nbsp;</p> <p>*This will match perfectly with my Santa's Milk bottles.&nbsp; Find them here:&nbsp;&nbsp;<a href="https://gobbybritdesigns.goimagine.com/holidays/christmas/milk-for-santa-engraved-bottles-with-lid-and-straw/">https://gobbybritdesigns.goima...</a></p> Santa, Santa's Milk & Cookies, Santa Tray, Milk, Cookies, Christmas, Treats for Santa, Treats, Engraved Tray, Christmas Traditions, Christmas Eve, Santa and Rudolph, Rudolph, carrot, plate A fun engraved Santa's Milk and Cookie Tray with spaces for cookies, milk and a carrot. Bamboo tray and engraved design Santa, Santa's Milk & Cookies, Santa Tray, Milk, Cookies, Christmas, Treats for Santa, Treats, Engraved Tray, Christmas Traditions, Christmas Eve, Santa and Rudolph, Rudolph, carrot, plate Santa's Milk and Cookie Tray <p><span style="color: rgb(141, 179, 226);"><em>All of our items are sandblast engraved and painted.&nbsp; No vinyl is used.</em></span></p> <p>All items are sandblast engraved. &nbsp;Sandblasting, or sand carving, is when compressed air flows through a hose forcibly propelling a stream of abrasive material (sand) onto a surface (mug/glass/tumbler, etc)</p> <p>Once the design is engraved, it is then painted with high quality acrylic paint.</p> <p>&nbsp;<b><i><span style="color: rgb(192, 80, 77);">Sandblast Engraving is permanent.&nbsp; All items are premium acrylic paint filled and matte sealed.&nbsp;</span></i></b></p> <p><b><i><span style="color: rgb(192, 80, 77);">While these are considered dishwasher safe, I recommend handwashing as all dishwashers vary in power and I want you to enjoy your mug for as long as possible.&nbsp;</span>&nbsp;</i></b></p> 14.95000000 118M,1070 2556 GobbyBritDesigns <h6><span style="text-align: justify; color: rgb(192, 80, 77);"><strong></strong></span></h6> <p class="text-justify"><span style="color: rgb(192, 80, 77);"><strong>Refunds and Exchanges</strong></span></p> <p><span style="color: rgb(31, 73, 125);">Unfortunately, custom orders cannot be returned or exchanged, unless damaged in transit.&nbsp; Therefore, when placing a custom order, please check all spelling, grammar and information before submitting.<br></span></p> <p><span style="color: rgb(31, 73, 125);">If your item arrives damaged, please let us know as soon as possible and provide a photograph of the damage.&nbsp;&nbsp;</span></p> <p class="text-justify"><b><span style="color: rgb(192, 80, 77);"></span></b><b><span style="color: rgb(192, 80, 77);">Custom and Personalized Orders<br></span></b><span style="color: rgb(31, 73, 125);">No problem, I love custom orders!&nbsp; &nbsp;Any item in my store can be customized (maybe with some limits, depending on customization required).&nbsp; &nbsp;Just message me and we can work on a custom order together.</span><b><span style="color: rgb(192, 80, 77);"><br></span></b></p> <p class="text-justify"><b><span style="color: rgb(192, 80, 77);">Shipping<br></span></b><span style="color: rgb(54, 96, 146);">All orders ship USPS First&nbsp;Class or USPS Priority Mail based on weight.</span></p> <p class="text-justify"><b><span style="color: rgb(192, 80, 77);">Processing Time<br></span></b><span style="color: rgb(54, 96, 146);">While I attempt to get your order to you as soon as possible, please remember that all items are made&nbsp;</span><i style="color: rgb(54, 96, 146);">to order</i><span style="color: rgb(54, 96, 146);">.&nbsp; Each item is processed and shipped as the order is processed.&nbsp;</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">However, I always attempt to ship within 5-7 days of receipt of your order.&nbsp; </span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">Should you need your item sooner, please send me a message.&nbsp; I will do everything I can to work with you to get you your item on time.</span> </p> <p><br></p> <h6></h6> santas-milk-and-cookie-tray 109/118 <p class="text-justify"><span style="color: rgb(54, 96, 146);">First things first, you are probably wondering about the name .. Gobby Brit Designs....</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">It's an English thing (yes, I'm English)&nbsp; The definition of "gobby" is '<em>a <span style="color: rgb(0, 0, 0);">person who talks too loudly in a blunt or opinionated way'</span> </em>and yes, I have been accused of that, a LOT!&nbsp; I have a blog about my time moving to the USA and it was very chatty (and opinionated) so what better name to give it than The Gobby Brit.&nbsp; My business was an extension of that blog and here we are.</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">I've been artsy-fartsy most of my life.&nbsp; I used to love to draw, but then life got in the way, as it usually does.&nbsp;&nbsp;Quite a few years ago I began screen printing shirts for a local motorcycle club and I also added embroidery along the way, I enjoyed it, and still do many orders, but I felt like something was missing.</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">In 2017 we were at a trade show and spotted a company selling "sand carving" equipment and my interest was peaked!&nbsp; We spoke to them for a long time, went to their quick class that day and ended up leaving the show as proud owners of a brand new sand carving system.&nbsp; A month later, we went to their weekend long training, gained our certificates and that, as they say was the beginning of a love affair!&nbsp;&nbsp;</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">We offer custom sandblast engraved mugs, tumblers, glass items and gifts.&nbsp; &nbsp;I can engrave on glass, wood, metal, ceramic - in fact, I've tried my hand at engraving many different substrates and my repertoire is increasing with each item I find I am able to engrave.</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">Custom Orders are very welcome.&nbsp; Please click the "Contact Us" button on any listing.<br><br>Please note all items are made to order so turn around times vary, but I try to get your item to you as soon as possible. If you need something in a particular timeframe, just let me know and I will work with you.</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">If you see something you like, but would like a different color, size or wording, please let me know. Also, if you have a specific personalization in mind , I can work with you on that too!!</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">Please, feel free to reach out via&nbsp;email or the message center with any questions or to request any customization.&nbsp;Thank you so much for your interest in my store.</span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">You can find me on: </span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">Facebook:&nbsp;<a href="http://www.facebook.com/gobbybritdesigns"><span></span></a><a href="http://www.facebook.com/gobbybritdesigns">www.facebook.com/gobbybritdesi...</a> </span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);">Instagram @gobbybritdesigns&nbsp; </span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);"><br></span></p> <p class="text-justify"><span style="color: rgb(54, 96, 146);"><br><br></span><br></p> D