mysql> SELECT p.prod_id,p.prod_code, p.prod_title,
p.prod_descr,p.prod_width,p.prod_depth, sr.prefix,sr.cur_symbol, srpp.price,round((coalesce(sr.cur_*srpp.price),2) as int_price, i.image_id,i.image_alt,i.image_name, i.image_width,i.image_height 
FROM 
(   
SELECT child.prod_id, (count(child.prod_id)  ) as cdepth   FROM product_categories as child   WHERE child.category_id IN (11,4)   GROUP BY child.prod_id ) as catprods, products p , shipping_regions_prod_price srpp, shipping_regions sr, products_images pi, images i WHERE cdepth = 2 AND p.prod_id = catprods.prod_id AND p.show_on_web = 1 AND catprods.prod_id = p.prod_id AND srpp.prod_id = p.prod_id AND srpp.sr_id = 4 AND srpp.sr_id = sr.sr_id AND p.prod_id = pi.prod_id AND pi.default_img = 'y' AND i.image_id = pi.image_id;
+----+-------------+------------+--------+-----------------------+---------+---------+-----------------------------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys         | key     | key_len | ref                               | rows | Extra                    |
+----+-------------+------------+--------+-----------------------+---------+---------+-----------------------------------+------+--------------------------+
|  1 | PRIMARY     | sr         | const  | PRIMARY               | PRIMARY | 4       | const                             |    1 |                          |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                  | NULL    | NULL    | NULL                              |    4 | Using where              |
|  1 | PRIMARY     | srpp       | eq_ref | sr_id_2,prod_id,sr_id | sr_id_2 | 8       | const,catprods.prod_id            |    1 |                          |
|  1 | PRIMARY     | p          | eq_ref | PRIMARY               | PRIMARY | 4       | catprods.prod_id                  |    1 | Using where              |
|  1 | PRIMARY     | pi         | ref    | prod_id,image_id      | prod_id | 4       | miniature_world_maker.p.prod_id   |    1 | Using where              |
|  1 | PRIMARY     | i          | eq_ref | PRIMARY               | PRIMARY | 4       | miniature_world_maker.pi.image_id |    1 |                          |
|  2 | DERIVED     | child      | index  | category_id           | prod_id | 8       | NULL                              |   12 | Using where; Using index |
+----+-------------+------------+--------+-----------------------+---------+---------+-----------------------------------+------+--------------------------+
7 rows in set (0.00 sec)