EverSQL Database Performance Knowledge Base

The optimization process and recommendations:

1. 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.
2. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
3. Avoid Subqueries (query line: 44): 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. Avoid Subqueries (query line: 56): 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.
5. 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.
6. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
7. Use Numeric Column Types For Numeric Values (query line: 17): Referencing a numeric value (e.g. 2) 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.
8. Use UNION ALL instead of UNION (query line: 33): 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 `boat_calendars` ADD INDEX `boat_calendars_idx_date_since` (`date_since`);
ALTER TABLE `boat_people` ADD INDEX `boat_people_idx_id` (`id`);
ALTER TABLE `boats` ADD INDEX `boats_idx_state_id_id_people_id` (`state_id`,`id`,`people_capacity_id`);
ALTER TABLE `countries` ADD INDEX `countries_idx_id` (`id`);
ALTER TABLE `element_types` ADD INDEX `element_types_idx_name_id` (`name`,`id`);
ALTER TABLE `provinces` ADD INDEX `provinces_idx_country_id` (`country_id`);
ALTER TABLE `states` ADD INDEX `states_idx_name_id` (`name`,`id`);
ALTER TABLE `translations` ADD INDEX `translations_idx_element_transla_element_id` (`element_translation`,`element_type_id`);

The optimized query:

SELECT
        DISTINCT b.* 
    FROM
        boats b,
        states s,
        boat_people bp,
        countries c,
        provinces pr,
        cities ct1,
        cities ct2,
        ports p,
        addresses a,
        translations t,
        element_types et 
    WHERE
        s.name = 'Confirmed' 
        AND bp.id = '2' 
        AND b.state_id = s.id 
        AND b.id NOT IN (
            SELECT
                bc_boat_id 
            FROM
                ((SELECT
                    bc.boat_id AS bc_boat_id 
                FROM
                    boat_calendars bc 
                WHERE
                    (
                        bc.date_since <= '2015-02-10 09:23:00 +0100' 
                        AND bc.date_until >= '2015-02-10 09:23:00 +0100'
                    )) 
            UNION
            DISTINCT (SELECT
                bc.boat_id AS bc_boat_id 
            FROM
                boat_calendars bc 
            WHERE
                (bc.date_since <= '2015-02-09 09:23:00 +0100' 
                AND bc.date_until >= '2015-02-09 09:23:00 +0100'))
        ) AS union1
    ) 
    AND b.people_capacity_id >= bp.id 
    AND c.id = (
        SELECT
            DISTINCT t.element_id 
        FROM
            translations t,
            element_types et 
        WHERE
            t.element_translation = 'Spain' 
            AND et.name = 'Country' 
            AND t.element_type_id = et.id
    ) 
    AND pr.country_id = c.id 
    AND pr.id = (
        SELECT
            DISTINCT t.element_id 
        FROM
            translations t,
            element_types et 
        WHERE
            t.element_translation = 'Mallorca' 
            AND et.name = 'Province' 
            AND t.element_type_id = et.id
    ) 
    AND (
        (
            ct1.province_id = pr.id 
            AND p.city_id = ct1.id 
            AND b.port_id = p.id
        ) 
        OR (
            ct2.province_id = pr.id 
            AND a.city_id = ct2.id 
            AND b.address_id = a.id
        )
    )