3
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?

3
  • I've edited the SQL to make more readable, just waiting for a peer review before you can see it.
    – Dave Rix
    Oct 25 '11 at 11:13
  • 1
    What's the point of doing a range between with the same IP in xlatedst and dst? Also can you post your indexes for the table and the explain output for the second query? Oct 25 '11 at 12:55
  • The point is, I need to know if that IP is either in dst, or in xlatedst :-) they dont contain the same IP's and the user can't know in which field the requested IP is. Meanwhile I've changed it to use the BETWEEN operator. Oct 27 '11 at 6:50
4

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 :)

3
  • While the query could stand some more refactoring, your answer is concise and seems very plausible to me. +1 !!! Oct 25 '11 at 15:33
  • 1
    It's a sledgehammer approach but it often does the trick. Oct 27 '11 at 15:30
  • 1
    Maybe a sledgehammer is just what he needs :-).
    – Marian
    Oct 27 '11 at 21:51
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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.