Optimize mysql query using various join and order by rand()

I was having trouble on my site, so I went look into mysql slow query log.

Fixed some indexing and also querys without joins now the server load got lower and its running fine.

I still have a query that I havent been able to fix, if any of you have a tip so I can make it better, I would appreciated it.

This is the query

SELECT p.product_id,
          p.product_offer_price,
          ps.subcategory_name,
          pb.brand_name,
          pm.model_name,
          pm.display_name,
          pm.year_from,
          pm.year_to 
        FROM product p
        INNER JOIN product_subcategory ps ON ps.subcategory_id = p.product_subcategory_id AND ps.subcategory_category_id = 12
        INNER JOIN product_stock pq ON pq.product_id = p.product_id AND pq.product_quantity > 0
        INNER JOIN product_photos pp ON pp.product_id = p.product_id
        INNER JOIN product_brand pb ON pb.brand_id = p.product_brand_id
        INNER JOIN product_model pm ON pm.model_id = p.product_model_id
        GROUP BY p.product_id
        ORDER BY RAND() LIMIT 4;