OR子句减慢SQL查询

对于以下查询，我需要您的帮助，该查询需要2分钟以上的时间才能返回结果：

SELECT 
    p.weight, 
    o.login,
    o.date, 
    o.s_address, 
    o.s_city, 
    o.s_county, 
    o.s_state, 
    o.s_country, 
    o.s_zipcode, 
    o.phone, 
    c.categoryid, 
    c.category, 
    o.orderid, 
    p.product product_name, 
    p.productcode sku, 
    d.amount, 
    v.value emplacement, 
    ( SELECT ev.value FROM xcart_extra_field_values ev LEFT JOIN xcart_extra_fields ef ON ef.fieldid=ev.fieldid WHERE ev.productid = d.productid AND ef.field = 'a_type' LIMIT 1 ) type, 
    o.customer_notes, 
    o.membership, 
    o.s_firstname, 
    o.s_lastname, 
    o.phone, 
    d.price, 
    o.email 
FROM `xcart_orders` o 
LEFT JOIN `xcart_shipping` s ON s.shippingid=o.shippingid 
LEFT JOIN `xcart_order_details` d ON d.orderid=o.orderid 
LEFT JOIN `xcart_products` p ON p.productid=d.productid 
LEFT JOIN `xcart_products_categories` pc ON pc.productid=p.productid 
LEFT JOIN `xcart_categories` c ON c.categoryid=pc.categoryid 
LEFT JOIN `xcart_extra_field_values` v ON v.productid=p.productid 
LEFT JOIN `xcart_extra_fields` f ON f.fieldid=v.fieldid 
WHERE o.shippingid IN ( SELECT DISTINCT shippingid FROM `xcart_rafale_shipping` WHERE rafale='1' ) 
AND ( 
    SELECT COUNT(*) 
    FROM `xcart_order_details` d2 
    LEFT JOIN `xcart_products_categories` pc2 ON pc2.productid=d2.productid 
    WHERE d2.orderid=o.orderid 
    AND pc2.categoryid NOT IN ( SELECT DISTINCT ac2.categoryid FROM `xcart_rafale_aggregation_categories` ac2 WHERE ac2.aggregationid='12' ) 
) = 0 
AND ( 
    ( o.date BETWEEN '1386802800' AND '1386889199' ) 
    OR (o.orderid IN ('44', '55', '66')) 
) 
AND o.orderid NOT IN ('11', '22', '33', '123', '458') 
AND o.paid = 'Y' 
AND o.status <> 'F' 
AND o.status <> 'Q' 
AND o.status <> 'I' 
AND f.field = 'emplacement' 
AND pc.main = 'Y' 
ORDER BY v.value ASC, p.productcode ASC 
LIMIT 100

问题可能来自以下子句

AND ( 
    ( o.date BETWEEN '1386802800' AND '1386889199' ) 
    OR (o.orderid IN ('44', '55', '66')) 
)

因为当我删除查询时查询执行得更快 OR (o.orderid IN ('44', '55', '66'))

在o.date和o.orderid列上都有索引

我使用了下面的查询，该查询的灵感来自@ Clockwork-Muse的回复：

SELECT 
    p.weight, 
    o.login,
    o.date, 
    o.s_address, 
    o.s_city, 
    o.s_county, 
    o.s_state, 
    o.s_country, 
    o.s_zipcode, 
    o.phone, 
    c.categoryid, 
    c.category, 
    o.orderid, 
    p.product product_name, 
    p.productcode sku, 
    d.amount, 
    v.value emplacement, 
    ( SELECT ev.value FROM xcart_extra_field_values ev LEFT JOIN xcart_extra_fields ef ON ef.fieldid=ev.fieldid WHERE ev.productid = d.productid AND ef.field = 'a_type' LIMIT 1 ) type, 
    o.customer_notes, 
    o.membership, 
    o.s_firstname, 
    o.s_lastname, 
    o.phone, 
    d.price, 
    o.email
FROM `xcart_order_details` d
INNER JOIN (SELECT *
            FROM `xcart_orders`
            WHERE (
                orderid IN ('44', '55', '66') 
                OR (`date` >= '1386802800' AND `date` <= '1386889199')
            )
            ) o
        ON o.orderid = d.orderid
INNER JOIN (SELECT DISTINCT shippingid
            FROM `xcart_rafale_shipping`
            WHERE rafale = '1') rf
        ON rf.shippingid = o.shippingid
INNER JOIN `xcart_shipping` s ON s.shippingid=o.shippingid  
INNER JOIN `xcart_products` p ON p.productid=d.productid
INNER JOIN (SELECT *
            FROM `xcart_products_categories`
            WHERE main = 'Y') pc
        ON pc.productid=p.productid
LEFT JOIN `xcart_categories` c ON c.categoryid=pc.categoryid
LEFT JOIN `xcart_extra_field_values` v ON v.productid=p.productid  
LEFT JOIN `xcart_extra_fields` f ON f.fieldid=v.fieldid
WHERE NOT EXISTS ( 
    SELECT d2.orderid 
    FROM `xcart_order_details` d2 
    LEFT JOIN `xcart_products_categories` pc2 ON pc2.productid=d2.productid 
    WHERE d2.orderid=o.orderid 
    AND pc2.categoryid NOT IN ( SELECT DISTINCT ac2.categoryid FROM `xcart_rafale_aggregation_categories` ac2 WHERE ac2.aggregationid='12' ) 
) 
AND o.orderid NOT IN ('11', '22', '33', '123', '458') 
AND o.paid = 'Y'
AND o.status NOT IN ('F', 'Q', 'I')
AND f.field = 'emplacement'