The optimization process and recommendations:

1. Avoid Correlated Subqueries (query line: 23): A correlated subquery is a subquery that contains a reference (column: h_id) 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: 30): A correlated subquery is a subquery that contains a reference (column: h_id) 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: 47): A correlated subquery is a subquery that contains a reference (column: h_id) 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: 63): A correlated subquery is a subquery that contains a reference (column: h_id) 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: 70): A correlated subquery is a subquery that contains a reference (column: h_id) 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: 95): A correlated subquery is a subquery that contains a reference (column: a_id) 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. 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.
8. 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'.
9. Use Numeric Column Types For Numeric Values (query line: 81): Referencing a numeric value (e.g. 4) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
10. Use Numeric Column Types For Numeric Values (query line: 91): Referencing a numeric value (e.g. 140) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
11. Use Numeric Column Types For Numeric Values (query line: 109): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
12. Use Numeric Column Types For Numeric Values (query line: 28): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
13. Use Numeric Column Types For Numeric Values (query line: 35): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
14. Use Numeric Column Types For Numeric Values (query line: 45): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
15. Use Numeric Column Types For Numeric Values (query line: 52): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
16. Use Numeric Column Types For Numeric Values (query line: 68): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
17. Use Numeric Column Types For Numeric Values (query line: 75): Referencing a numeric value (e.g. 100) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
18. Use Numeric Column Types For Numeric Values (query line: 101): Referencing a numeric value (e.g. 140) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
19. Use Numeric Column Types For Numeric Values (query line: 103): Referencing a numeric value (e.g. 140) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.

Optimal indexes for this query:

ALTER TABLE `locations` ADD INDEX `locations_idx_company_id_h_id` (`company_id`,`h_id`);
ALTER TABLE `orders` ADD INDEX `orders_idx_a_id` (`a_id`);
ALTER TABLE `suborders` ADD INDEX `suborders_idx_order_user_day_da_compan_ta_id` (`order_type`,`user_id`,`day_date`,`company_id`,`ta_id`);
ALTER TABLE `suborders` ADD INDEX `suborders_idx_order_type_a_id_pos_id` (`order_type`,`a_id`,`pos_id`);
ALTER TABLE `suborders` ADD INDEX `suborders_idx_pos_id` (`pos_id`);
ALTER TABLE `suborders` ADD INDEX `suborders_idx_user_day_da_order_a_id_pos_id` (`user_id`,`day_date`,`order_type`,`a_id`,`pos_gesamt_id`);

The optimized query:

SELECT
        DISTINCT tax.ta_id,
        tax.a_id,
        ax.status,
        ax.kunden_id,
        IF(ax.todo_from != '0000-00-00',
        DATE_FORMAT(ax.todo_from,
        '%d.%m'),
        'k. day_date') todo_from,
        IF(ax.todo_until != '0000-00-00',
        DATE_FORMAT(ax.todo_until,
        '%d.%m'),
        'k. day_date') todo_until,
        IF((SELECT
            taj.city 
        FROM
            suborders taj 
        WHERE
            taj.a_id = tax.a_id 
            AND taj.order_type = 'BRING' 
        ORDER BY
            taj.pos_id ASC LIMIT 1) != '',
        CONCAT(IF((SELECT
            locations.short_name 
        FROM
            locations 
        WHERE
            locations.company_id = '100' 
            AND locations.h_id = tax.h_id) != '',
        (SELECT
            locations.short_name 
        FROM
            locations 
        WHERE
            locations.company_id = '100' 
            AND locations.h_id = tax.h_id),
        tax.city),
        '>',
        CONCAT((SELECT
            IF((SELECT
                locations.short_name 
            FROM
                locations 
            WHERE
                locations.company_id = '100' 
                AND locations.h_id = taj.h_id) != '',
            (SELECT
                locations.short_name 
            FROM
                locations 
            WHERE
                locations.company_id = '100' 
                AND locations.h_id = taj.h_id),
            taj.city) 
        FROM
            suborders taj 
        WHERE
            taj.a_id = tax.a_id 
            AND taj.order_type = 'BRING' 
            AND taj.pos_id >= tax.pos_id 
        ORDER BY
            taj.pos_id ASC LIMIT 1))),
        IF((SELECT
            locations.short_name 
        FROM
            locations 
        WHERE
            locations.company_id = '100' 
            AND locations.h_id = tax.h_id) != '',
        (SELECT
            locations.short_name 
        FROM
            locations 
        WHERE
            locations.company_id = '100' 
            AND locations.h_id = tax.h_id),
        tax.city)) AS city,
        tax.user_id,
        tax.day_date,
        tax.pos_gesamt_id,
        '4' AS class_type 
    FROM
        suborders tax 
    INNER JOIN
        orders ax 
            ON (
                ax.a_id = tax.a_id
            ) 
    WHERE
        tax.order_type = 'TAKE' 
        AND tax.user_id = '140' 
        AND (
            tax.day_date = '2013-04-16' 
            AND '2013-04-16' = (
                SELECT
                    taj.day_date 
                FROM
                    suborders taj 
                WHERE
                    taj.a_id = tax.a_id 
                    AND taj.user_id = '140' 
                    AND taj.day_date = '2013-04-16' 
                    AND taj.user_id = '140' 
                    AND taj.order_type = 'BRING' 
                ORDER BY
                    taj.pos_gesamt_id ASC
            )
        ) 
        AND tax.company_id = '100' 
    GROUP BY
        tax.ta_id 
    ORDER BY
        NULL