The optimization process and recommendations:

1. 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. '%Avenue%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
2. Avoid Subqueries (query line: 18): 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.
3. Avoid Subqueries (query line: 33): 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.
4. 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.
5. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
6. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `Y`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
7. 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.

Optimal indexes for this query:

ALTER TABLE `customers` ADD INDEX `customers_idx_name` (`name`);
ALTER TABLE `customers_addresses` ADD INDEX `customers_addresse_idx_id_customer` (`id_customer`);
ALTER TABLE `customers_contacts` ADD INDEX `customers_contacts_idx_id_customer` (`id_customer`);

The optimized query:

SELECT
        customers_id_customer,
        customers_name,
        X.contact AS contact,
        Y.street,
        Y.zipcode,
        Y.city 
    FROM
        (SELECT
            customers.id_customer AS customers_id_customer,
            customers.name AS customers_name 
        FROM
            customers 
        ORDER BY
            customers.name DESC LIMIT 20) AS customers 
    LEFT JOIN
        (
            SELECT
                GROUP_CONCAT(CONCAT(customers_contacts.type,
                ': ',
                customers_contacts.value) SEPARATOR ', ') AS contact,
                customers_contacts.id_customer 
            FROM
                customers_contacts 
            GROUP BY
                customers_contacts.id_customer 
            ORDER BY
                NULL
        ) AS X 
            ON X.id_customer = customers.customers_id_customer 
    INNER JOIN
        (
            SELECT
                GROUP_CONCAT(street SEPARATOR '
') AS street,
                GROUP_CONCAT(zipcode SEPARATOR '
') AS zipcode,
                GROUP_CONCAT(city SEPARATOR '
') AS city,
                customers_addresses.id_customer 
            FROM
                customers_addresses 
            GROUP BY
                customers_addresses.id_customer 
            ORDER BY
                NULL
        ) AS Y 
            ON Y.id_customer = customers.customers_id_customer 
    WHERE
        Y.street LIKE '%Avenue%' LIMIT 20