[Solved] SQL optimization (MySQL)
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

SQL optimization (MySQL)

Database type:

I know it´s difficult to answer without knowing the model, but I have next heavy query that takes around 10 secs to complete in my MySQL database. I guess it can be optimized, but I´m not that skilled.

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
            boat_calendars bc
        WHERE
            (date(bc.since) <= '2015-02-09 09:23:00 +0100'
                AND date(bc.until) >= '2015-02-09 09:23:00 +0100')
                OR (date(bc.since) <= '2015-02-10 09:23:00 +0100'
                AND date(bc.until) >= '2015-02-10 09:23:00 +0100'))
        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)); 

Basically, it tries to get all the boats, that are not already booked in Confirmed state and that are in a province and a country ie. Mallorca, Spain.

Please, let me know if you need some more details about de purpose of the query or the model.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
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
        )
    )

Related Articles



* original question posted on StackOverflow here.