MySQL: 2 separate queries run very fast, combined query with OR runs very slow

Question

EXPLAIN 
SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
    firewall.name as fw, proto.proto, service, action.action, 
    sum(count) as hits, max(date) as lastused, rule_uid.rule_uid 
FROM log, action, firewall, proto, rule_uid 
WHERE ( rule_uid.id = log.rule_uid ) 
    AND ( proto.id = log.proto ) 
    AND ( log.action = action.id AND action.action='accept' ) 
    AND ( log.orig = firewall.id) 
    AND ( ( src >= inet_aton('0.0.0.0') 
            AND src <= inet_aton('255.255.255.255') ) )
    AND ( ( xlatedst >= inet_aton('173.82.122.94') 
            AND xlatedst <= inet_aton('173.82.122.94') ) ) 
GROUP BY src, dst 
ORDER BY hits DESC, dst 
LIMIT 100;

uses temporary/filesort, 92 lines, and runs in 30ms.

Similarly fast if I modify the query to search for the same IP in the dst column.

But if I combine both queries to search for that IP in the xlatedst OR the dst columns:

SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest, 
    firewall.name as fw,  proto.proto, service, action.action, 
    sum(count) as hits, max(date) as lastused, rule_uid.rule_uid 
FROM log, action, firewall, proto, rule_uid 
WHERE( rule_uid.id = log.rule_uid ) 
    AND ( proto.id = log.proto ) 
    AND ( log.action = action.id AND action.action='accept' )
    AND ( log.orig = firewall.id) 
    AND ( ( src >= inet_aton('0.0.0.0') 
            AND src <= inet_aton('255.255.255.255') ) ) 
    AND *( ( xlatedst >= inet_aton('173.82.122.94') 
             AND xlatedst <= inet_aton('173.82.122.94') )
         OR ( dst >= inet_aton('173.82.122.94') 
              AND dst <= inet_aton('173.82.122.94') ) 
         )* 
GROUP BY src, dst 
ORDER BY hits DESC, dst
LIMIT 100;

then it examines 2121544 rows and runs for 3 minutes.

Now I could just run both queries and concat the results. But I wonder if there is a simpler way?

There is a seperate index on both "dst" and "xlatedst". Why isnt it used?

Answer 1

I'm not sure why, but after messing around for a couple hours (changing several things in parallel) the queries now all run very fast (<1 sec). This is a current example of what it looks like now: (sorry I'm unfamiliar with the formatting to make it readable) (I didnt change anything in the database, only the query string)

SELECT
    inet_ntoa(src) as source,
    inet_ntoa(xlatesrc) as xlate_src,
    inet_ntoa(dst) as dest,
    inet_ntoa(xlatedst) as xlate_dst,
    firewall.name as fw,
    proto.proto, service,
    action.action, sum(count) as hits,
    max(date) as lastused,
    rule_uid.rule_uid
FROM
    log, action, firewall, proto, rule_uid
WHERE
    ( rule_uid.id = log.rule_uid ) AND
        ((src >= inet_aton('1.1.1.1') AND src <= inet_aton('255.255.255.255'))
        OR
        (xlatesrc >= inet_aton('1.1.1.1') AND xlatesrc <= inet_aton('255.255.255.255'))
    ) AND
    (
        (dst >= inet_aton('173.82.122.94') AND dst <= inet_aton('173.82.122.94'))
        OR
        (xlatedst >= inet_aton('173.82.122.94') AND xlatedst <= inet_aton('173.82.122.94'))
    ) AND
    (proto.id = log.proto) AND
    (log.action = action.id AND action.action='accept')  AND
    ( log.orig = firewall.id )
GROUP BY
    fw, proto, service
ORDER BY
    hits DESC, dst
LIMIT
    100
;

Answer 2

In my experience though, doing anything with an OR condition can have horrible implications when running queries, have you considered running this as two separate queries and then combining the results?

E.g.

SELECT * FROM (

    SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
        firewall.name as fw, proto.proto, service, action.action, 
        sum(count) as hits, max(date) as lastused, rule_uid.rule_uid 
    FROM log, action, firewall, proto, rule_uid 
    WHERE ( rule_uid.id = log.rule_uid ) 
            AND ( proto.id = log.proto ) 
        AND ( log.action = action.id AND action.action='accept' ) 
        AND ( log.orig = firewall.id) 
        AND ( ( src >= inet_aton('0.0.0.0') 
                AND src <= inet_aton('255.255.255.255') ) )
        AND ( ( xlatedst >= inet_aton('173.82.122.94') 
                AND xlatedst <= inet_aton('173.82.122.94') ) ) 
    GROUP BY src, dst 
    ORDER BY hits DESC, dst

    UNION ALL

    SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
        firewall.name as fw, proto.proto, service, action.action, 
        sum(count) as hits, max(date) as lastused, rule_uid.rule_uid 
    FROM log, action, firewall, proto, rule_uid 
    WHERE ( rule_uid.id = log.rule_uid ) 
        AND ( proto.id = log.proto ) 
        AND ( log.action = action.id AND action.action='accept' ) 
        AND ( log.orig = firewall.id) 
        AND ( ( src >= inet_aton('0.0.0.0') 
                AND src <= inet_aton('255.255.255.255') ) )
        AND ( ( dst >= inet_aton('173.82.122.94') 
              AND dst <= inet_aton('173.82.122.94') ) ) 
    GROUP BY src, dst 
    ORDER BY hits DESC, dst

) ORDER BY hits DESC, dst
LIMIT 100;

That may do the job, or something similar :)