The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `attrib_pegi` ADD INDEX `attrib_pegi_idx_id` (`id`);
ALTER TABLE `attrib_region` ADD INDEX `attrib_region_idx_id` (`id`);
ALTER TABLE `product_category_main` ADD INDEX `product_main_idx_id` (`id`);
ALTER TABLE `product_category_sub` ADD INDEX `product_sub_idx_id` (`id`);
ALTER TABLE `product_condition` ADD INDEX `product_condition_idx_id` (`id`);
ALTER TABLE `product_data` ADD INDEX `product_data_idx_category_id_title` (`category_main_id`,`title`);

The optimized query:

SELECT
        product_data.id AS id,
        product_data.sku AS sku,
        product_data.barcode AS barcode,
        product_data.title AS title,
        product_data.attrib_releasedate AS attrib_releasedate,
        product_category_main.id AS category_main_id,
        product_category_main.name AS category_main_description,
        product_category_sub.id AS category_sub_id,
        product_category_sub.name AS category_sub_description,
        product_category_sub.description_short AS category_sub_description_short,
        product_condition.id AS condition_id,
        product_condition.name AS condition_description,
        attrib_pegi.name AS attrib_pegi,
        attrib_region.name AS attrib_region,
        product_data.asin AS asin,
        product_data.cex AS cex 
    FROM
        product_data 
    INNER JOIN
        product_category_main 
            ON product_data.category_main_id = product_category_main.id 
    INNER JOIN
        product_category_sub 
            ON product_data.category_sub_id = product_category_sub.id 
    LEFT JOIN
        product_condition 
            ON product_data.condition_id = product_condition.id 
    LEFT JOIN
        attrib_pegi 
            ON attrib_pegi.id = product_data.attrib_pegi 
    LEFT JOIN
        attrib_region 
            ON attrib_region.id = product_data.attrib_region 
    WHERE
        product_data.title <> '' 
        AND product_category_main.id = 1 
    ORDER BY
        category_main_id,
        product_data.title