Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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?

share|improve this question
    
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? –  Derek Downey 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. –  Ethan Arnold Oct 27 '11 at 6:50
add comment

2 Answers

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

share|improve this answer
    
While the query could stand some more refactoring, your answer is concise and seems very plausible to me. +1 !!! –  RolandoMySQLDBA Oct 25 '11 at 15:33
1  
It's a sledgehammer approach but it often does the trick. –  Mark Storey-Smith Oct 27 '11 at 15:30
1  
Maybe a sledgehammer is just what he needs :-). –  Marian Oct 27 '11 at 21:51
add comment

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
;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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