#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0

    Help implifying a 3 way join


    This query works fine but takes ages, i was wondering if anybody had any quicker ways to do this, i just want the end result to be either true or 3,2,1(being the highest) .

    SELECT * FROM event JOIN iptbl ON iptbl.ip ='123.23.21'JOIN signature ON signature.cid = event.cid WHERE signature.priorty = 1

    Event table
    Id | cid | sig_id
    ----------------
    1 | 1 | 7
    2 | 2 | 6

    Iptbl table
    Id | cid | ip
    -----------
    1 | 1 | 123.23.21
    2 | 2 | 123.23.21

    Signature table
    Id | priority | cid
    i--------------
    1 | 3 | 1
    2 | 2 | 2

    There are thousands of rows for ips and events the database cannot be restructered because its hardcoded from into the program.

    Thanks foryour help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    please do an EXPLAIN on the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Sorry, What i want to do is find out the highest priority in the signature table but i only have an IP and there is no link between the tables so i have to find out which of the signatures have been triggered from the events tables using the cid in the iptable then get the signature id from the events table to then check the signature table to find out the priority of that signature

    The ip table and events table could thousands of the same ip and event
    but just i want to find out the highest priority.

    I hope that makes sense.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by djwebd
    I hope that makes sense.
    nope, sorry, it didn't

    when i asked you to do an EXPLAIN on the query, i did not mean an explanation in words, i meant an EXPLAIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Ah well I guess that didn't help at all then, how do I do an explain I have never done one before? Is it just explain instead of select?

    Thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by djwebd
    Is it just explain instead of select?
    no, in front of, not instead of

    that page i linked to will, um, explain how to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Here is the explain query:

    Code:
    EXPLAIN SELECT COUNT( * ) AS `numrows`
    FROM (
    `event`
    )
    LEFT JOIN `iphdr` ON `iphdr`.`cid` = `event`.`cid`
    JOIN `signature` ON `event`.`signature` = `signature`.`sig_id`
    WHERE `iphdr`.`ip_src` = '2887648015'
    AND `signature`.`sig_priority` = '1'
    RESULTS:
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE iphdr ref ip_src ip_src 4 const 1727 Using where; Using index
    1 SIMPLE event index sig sig 4 NULL 2031 Using where; Using index; Using join buffer
    1 SIMPLE signature eq_ref PRIMARY 4 snort2.event.signature 1 Using where

    Hope this helps
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    first of all, that's not the query you posted earlier

    your new one makes a lot more sense as it has join conditions that appear to be correct

    secondly, you'll notice the first line of the EXPLAIN is for the iphdr table

    that's because of the WHERE condition which isolates a specific value of the ip_src column

    mysql is smart enough to have ignored your LEFT JOIN and turned it into an inner join

    you've also neglected to mention how fast this new query is

    judging from the EXPLAIN i'd say it's optimized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi,

    Sorry for the confusion, I was on my tab last night as the mrs banned me from doing any more work so I was trying to rewrite the query from my head.

    This query works fine like I said but it takes ages because return a result because of the tables it has to go through, I am wondering if a count is the right thing to have and maybe use a select limit? I will try this now.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    OK right I have managed to cut down the query time a considerable amount:

    Code:
    SELECT `signature`.`sig_priority` FROM (`event`) LEFT JOIN `iphdr` ON `iphdr`.`cid` = `event`.`cid` JOIN `signature` ON `event`.`signature` = `signature`.`sig_id` WHERE `iphdr`.`ip_src` = '2887648015' AND `signature`.`sig_priority` = '1' LIMIT 1
    It doesn't matter what I actually select I just want to find out if there is a row at the end, is there any quicker way to do this query?

    Also there mog
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by djwebd
    is there any quicker way to do this query?
    please allow me to give you some advice on how to post a query for review

    1. use indentation and line breaks and get rid of those evil backticks
    2. do a SHOW CREATE TABLE for every table involved, so that we can see the indexes
    3. do an EXPLAIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo