SELECT 
  cscart_banners.banner_id, 
  cscart_banners.type, 
  cscart_banners.target, 
  cscart_banners.banner_height_desktop, 
  cscart_banners.banner_height_tablet, 
  cscart_banners.banner_height_mobile, 
  cscart_banners.status, 
  cscart_banners.timestamp, 
  cscart_banner_descriptions.banner, 
  cscart_banner_descriptions.description, 
  cscart_banner_descriptions.url 
FROM 
  cscart_banners 
  LEFT JOIN cscart_banner_descriptions ON cscart_banner_descriptions.banner_id = cscart_banners.banner_id 
  AND cscart_banner_descriptions.lang_code = 'en' 
  LEFT JOIN (
    SELECT 
      banner_id, 
      COUNT(*) as image_count 
    FROM 
      cscart_banner_images 
    WHERE 
      lang_code = 'en' 
    GROUP BY 
      banner_id
  ) AS banner_image_count ON banner_image_count.banner_id = cscart_banners.banner_id 
WHERE 
  1 
  AND (
    cscart_banners.type != 'G' 
    OR banner_image_count.image_count > 0
  ) 
  AND cscart_banners.status = 'A' 
GROUP BY 
  cscart_banners.banner_id 
ORDER BY 
  cscart_banners.timestamp desc 
LIMIT 
  0, 6

Query time 0.00234

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "9.60"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_banners",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "status"
              ],
              "key": "status",
              "used_key_parts": [
                "status"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 2,
              "rows_produced_per_join": 2,
              "filtered": "100.00",
              "index_condition": "(`pankajecarter_systemtwo`.`cscart_banners`.`status` <=> 'A')",
              "cost_info": {
                "read_cost": "2.00",
                "eval_cost": "0.40",
                "prefix_cost": "2.40",
                "data_read_per_join": "1K"
              },
              "used_columns": [
                "banner_id",
                "status",
                "type",
                "target",
                "banner_height_desktop",
                "banner_height_tablet",
                "banner_height_mobile",
                "timestamp"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_banner_descriptions",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "banner_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "pankajecarter_systemtwo.cscart_banners.banner_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 2,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "2.00",
                "eval_cost": "0.40",
                "prefix_cost": "4.80",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "banner_id",
                "banner",
                "url",
                "description",
                "lang_code"
              ]
            }
          },
          {
            "table": {
              "table_name": "banner_image_count",
              "access_type": "ref",
              "possible_keys": [
                "<auto_key0>"
              ],
              "key": "<auto_key0>",
              "used_key_parts": [
                "banner_id"
              ],
              "key_length": "3",
              "ref": [
                "pankajecarter_systemtwo.cscart_banners.banner_id"
              ],
              "rows_examined_per_scan": 2,
              "rows_produced_per_join": 4,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "4.00",
                "eval_cost": "0.80",
                "prefix_cost": "9.60",
                "data_read_per_join": "96"
              },
              "used_columns": [
                "banner_id",
                "image_count"
              ],
              "attached_condition": "<if>(found_match(banner_image_count), ((`pankajecarter_systemtwo`.`cscart_banners`.`type` <> 'G') or (`banner_image_count`.`image_count` > 0)), true)",
              "materialized_from_subquery": {
                "using_temporary_table": true,
                "dependent": false,
                "cacheable": true,
                "query_block": {
                  "select_id": 2,
                  "cost_info": {
                    "query_cost": "6.53"
                  },
                  "grouping_operation": {
                    "using_filesort": false,
                    "table": {
                      "table_name": "cscart_banner_images",
                      "access_type": "index",
                      "possible_keys": [
                        "banner_layer",
                        "banner_id"
                      ],
                      "key": "banner_layer",
                      "used_key_parts": [
                        "banner_id",
                        "layer_key",
                        "lang_code"
                      ],
                      "key_length": "161",
                      "rows_examined_per_scan": 22,
                      "rows_produced_per_join": 2,
                      "filtered": "10.00",
                      "using_index": true,
                      "cost_info": {
                        "read_cost": "6.09",
                        "eval_cost": "0.44",
                        "prefix_cost": "6.53",
                        "data_read_per_join": "369"
                      },
                      "used_columns": [
                        "banner_id",
                        "lang_code"
                      ],
                      "attached_condition": "(`pankajecarter_systemtwo`.`cscart_banner_images`.`lang_code` = 'en')"
                    }
                  }
                }
              }
            }
          }
        ]
      }
    }
  }
}