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.