The optimization process and recommendations:

1. Avoid Correlated Subqueries (query line: 22): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
2. Avoid Correlated Subqueries (query line: 31): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
3. Avoid Correlated Subqueries (query line: 40): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
4. Avoid Correlated Subqueries (query line: 49): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
5. Avoid Correlated Subqueries (query line: 58): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
6. Avoid Correlated Subqueries (query line: 67): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
7. Avoid Correlated Subqueries (query line: 76): A correlated subquery is a subquery that contains a reference (column: pid) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
8. Avoid LIKE Searches With Leading Wildcard (query line: 18): The database will not use an index when using like searches with a leading wildcard (e.g. '%red christmas napkin \n red xmas napkin red christmas napkins red xmas napkins red christmas \n serviette red xmas serviette red christmas serviettes red xmas serviettes%'). 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: 29): 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 LIKE Searches With Leading Wildcard (query line: 38): The database will not use an index when using like searches with a leading wildcard (e.g. '%christmas%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
11. Avoid LIKE Searches With Leading Wildcard (query line: 47): The database will not use an index when using like searches with a leading wildcard (e.g. '%napkin%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
12. Avoid LIKE Searches With Leading Wildcard (query line: 56): The database will not use an index when using like searches with a leading wildcard (e.g. '%xmas%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
13. Avoid LIKE Searches With Leading Wildcard (query line: 65): The database will not use an index when using like searches with a leading wildcard (e.g. '%napkins%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
14. Avoid LIKE Searches With Leading Wildcard (query line: 74): The database will not use an index when using like searches with a leading wildcard (e.g. '%serviette%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
15. Avoid LIKE Searches With Leading Wildcard (query line: 83): The database will not use an index when using like searches with a leading wildcard (e.g. '%serviettes%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
16. Avoid OFFSET In LIMIT Clause (query line: 99): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
17. 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.
18. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `cat`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.
19. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `brand`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.

Optimal indexes for this query:

ALTER TABLE `prod` ADD INDEX `prod_idx_live_deleted_pid` (`live`,`deleted`,`pid`);
ALTER TABLE `prod_link_cat` ADD INDEX `prod_cat_idx_pid` (`pid`);

The optimized query:

SELECT
        p.pid,
        (((MATCH (prod.title) AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' IN BOOLEAN MODE)) * 10) + ((MATCH (prod.code) AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' IN BOOLEAN MODE)) * 0.3) + ((MATCH (prod.description) AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' IN BOOLEAN MODE)) * 0.2) + ((MATCH (prod.search_keywords) AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' IN BOOLEAN MODE)) * 5) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%red christmas napkin 
                red xmas napkin red christmas napkins red xmas napkins red christmas 
                serviette red xmas serviette red christmas serviettes red xmas serviettes%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%red%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10.857142857143) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%christmas%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10.714285714286) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%napkin%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10.571428571429) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%xmas%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10.428571428571) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%napkins%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10.285714285714) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%serviette%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10.142857142857) + (IFNULL((SELECT
            10 
        FROM
            prod 
        WHERE
            prod.live = 1 
            AND prod.deleted = 0 
            AND prod.title LIKE '%serviettes%' 
            AND prod.pid = p.pid LIMIT 1),
        0) * 10)) AS 'relevance' 
    FROM
        prod p 
    LEFT JOIN
        prod_link_cat 
            ON prod_link_cat.pid = p.pid 
    WHERE
        p.live = 1 
        AND p.deleted = 0 
    GROUP BY
        p.pid 
    HAVING
        relevance > 0 
    ORDER BY
        prod_link_cat.position ASC LIMIT 0,
        12