The optimization process and recommendations:

1. Avoid LIKE Searches With Leading Wildcard (query line: 72): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
2. Avoid LIKE Searches With Leading Wildcard (query line: 113): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
3. Avoid LIKE Searches With Leading Wildcard (query line: 114): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
4. Avoid LIKE Searches With Leading Wildcard (query line: 115): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
5. Avoid LIKE Searches With Leading Wildcard (query line: 116): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
6. Avoid LIKE Searches With Leading Wildcard (query line: 117): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
7. Avoid LIKE Searches With Leading Wildcard (query line: 118): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
8. Avoid LIKE Searches With Leading Wildcard (query line: 119): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
9. Avoid LIKE Searches With Leading Wildcard (query line: 120): The database will not use an index when using like searches with a leading wildcard (e.g. '%red%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
10. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
11. Avoid Subqueries (query line: 17): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
12. 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.
13. Sort and Limit Before Joining (modified query below): In cases where the joins aren't filtering any rows, it's possible to sort and limit the amount of rows using a subquery in the FROM clause, before applying the joins to all other tables.
14. Use UNION ALL instead of UNION (query line: 76): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.

Optimal indexes for this query:

ALTER TABLE `artists` ADD INDEX `artists_idx_id` (`id`);
ALTER TABLE `artists_products` ADD INDEX `artists_products_idx_product_id` (`product_id`);
ALTER TABLE `categories` ADD INDEX `categories_idx_id` (`id`);
ALTER TABLE `categories_products` ADD INDEX `categories_product_idx_product_id` (`product_id`);
ALTER TABLE `colors` ADD INDEX `colors_idx_id` (`id`);
ALTER TABLE `colors_family` ADD INDEX `colors_family_idx_id` (`id`);
ALTER TABLE `colors_products` ADD INDEX `colors_products_idx_colors_id` (`colors_id`);
ALTER TABLE `colors_products` ADD INDEX `colors_products_idx_product_id` (`product_id`);
ALTER TABLE `keywords` ADD INDEX `keywords_idx_id` (`id`);
ALTER TABLE `products` ADD INDEX `products_idx_active` (`active`);
ALTER TABLE `products_keywords` ADD INDEX `products_keywords_idx_product_id` (`product_id`);

The optimized query:

SELECT
        p_slug,
        p_image_code,
        `products_name`,
        `authors_name`,
        `category_name`,
        `color_family_name` 
    FROM
        ((SELECT
            DISTINCT p_slug AS p_slug,
            p_image_code AS p_image_code,
            `products_name` AS `products_name`,
            `a`.`name` AS `authors_name`,
            `c`.`name` AS `category_name`,
            `cf`.`name` AS `color_family_name` 
        FROM
            (SELECT
                `p`.`slug` AS p_slug,
                `p`.`image_code` AS p_image_code,
                `p`.`name` AS `products_name`,
                `p`.`id` AS p_id 
            FROM
                `products` AS `p` 
            WHERE
                `p`.`active` = 1 LIMIT 60) AS `p` 
        INNER JOIN
            `categories_products` AS `cp` 
                ON (
                    `p`.p_id = `cp`.`product_id`
                ) 
        INNER JOIN
            `categories` AS `c` 
                ON (
                    `cp`.`categories_id` = `c`.`id`
                ) 
        INNER JOIN
            `artists_products` AS `ap` 
                ON (
                    `p`.p_id = `ap`.`product_id`
                ) 
        INNER JOIN
            `artists` AS `a` 
                ON (
                    `ap`.`artists_id` = `a`.`id`
                ) 
        INNER JOIN
            `products_keywords` AS `pk` 
                ON (
                    `p`.p_id = `pk`.`product_id`
                ) 
        INNER JOIN
            `keywords` AS `kw` 
                ON (
                    `pk`.`keyword_id` = `kw`.`id`
                ) 
        INNER JOIN
            `colors_products` AS `cop` 
                ON (
                    `p`.p_id = `cop`.`product_id`
                ) 
        INNER JOIN
            `colors` AS `col` 
                ON (
                    `cop`.`colors_id` = `col`.`id`
                ) 
        INNER JOIN
            `colors_family` AS `cf` 
                ON (
                    `col`.`color_family_id` = `cf`.`id`
                ) 
        WHERE
            `col`.`pantone_code` LIKE '%red%' 
            AND 1 = 1 LIMIT 60
        ) 
    UNION
    DISTINCT (SELECT
        DISTINCT `p`.`slug` AS p_slug,
        `p`.`image_code` AS p_image_code,
        `p`.`name` AS `products_name`,
        `a`.`name` AS `authors_name`,
        `c`.`name` AS `category_name`,
        `cf`.`name` AS `color_family_name` 
    FROM
        `products` AS `p` 
    INNER JOIN
        `categories_products` AS `cp` 
            ON (`p`.`id` = `cp`.`product_id`) 
    INNER JOIN
        `categories` AS `c` 
            ON (`cp`.`categories_id` = `c`.`id`) 
    INNER JOIN
        `artists_products` AS `ap` 
            ON (`p`.`id` = `ap`.`product_id`) 
    INNER JOIN
        `artists` AS `a` 
            ON (`ap`.`artists_id` = `a`.`id`) 
    INNER JOIN
        `products_keywords` AS `pk` 
            ON (`p`.`id` = `pk`.`product_id`) 
    INNER JOIN
        `keywords` AS `kw` 
            ON (`pk`.`keyword_id` = `kw`.`id`) 
    INNER JOIN
        `colors_products` AS `cop` 
            ON (`p`.`id` = `cop`.`product_id`) 
    INNER JOIN
        `colors` AS `col` 
            ON (`cop`.`colors_id` = `col`.`id`) 
    INNER JOIN
        `colors_family` AS `cf` 
            ON (`col`.`color_family_id` = `cf`.`id`) 
    WHERE
        `p`.`image_code` LIKE '%red%' 
        OR `p`.`slug` LIKE '%red%' 
        OR `p`.`name` LIKE '%red%' 
        OR `a`.`name` LIKE '%red%' 
        OR `c`.`name` LIKE '%red%' 
        OR `kw`.`name` LIKE '%red%' 
        OR `col`.`name` LIKE '%red%' 
        OR `cf`.`name` LIKE '%red%' LIMIT 60)
) AS union1 LIMIT 60