SQL optimization (MySQL)

Question

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.

remove * instead give column names in select statement. it will increase some performance. Its one of the way to optimize — Brave Soul
– Brave Soul, Commented Feb 6, 2015 at 9:01
You have cross joins against translations and element_types . Neither of these tables are used in the main query and there is no join condition against them to limit the joins. — Kickstart
– Kickstart, Commented Feb 6, 2015 at 9:42
Fix your query to have proper, explicit join syntax. First, it is hard to understand. Second, you could be missing join conditions between tables. — Gordon Linoff
– Gordon Linoff, Commented Feb 6, 2015 at 11:43

Brave Soul · Accepted Answer · 2015-02-06 10:13:57Z

1

remove * from select clause. instead give column names in select clause. it will increase some performance. Its one of the way to optimize

answered Feb 6, 2015 at 10:13

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gajendra Bang · Accepted Answer · 2015-02-06 09:10:42Z

1

Instead of having a sub query, use LEFT JOIN NULL (just google for it) and it will help a lot.

answered Feb 6, 2015 at 9:10

Gajendra Bang

3,5732 gold badges29 silver badges32 bronze badges

Comments

Rober · Accepted Answer · 2015-02-06 09:15:57Z

0

All your answers are good. But, according to @POHH suggestions, I increased the performance magically by just replacing the b.* for b.somecolumnsnames.

From 10 to 1 or 2 secs.

answered Feb 6, 2015 at 9:15

Rober

6,15818 gold badges62 silver badges118 bronze badges

1 Comment

Brave Soul Over a year ago

you can check it as answer then ... :p

Collectives™ on Stack Overflow

SQL optimization (MySQL)

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related