Hi again Florin, ----- Original Message ----- From: "Florin Andrei" <[EMAIL PROTECTED]>
> SELECT event.cid, iphdr.ip_src, iphdr.ip_dst, tcphdr.tcp_dport FROM > event, iphdr, tcphdr WHERE event.cid = iphdr.cid AND event.cid = > tcphdr.cid AND tcphdr.tcp_flags = '2'; Your only search condition is tcphdr.tcp_flags=2. So you will want to put the tcphdr first in the join and have this condition evaluated first (re-order your query and do EXPLAIN SELECT ....), otherwise the server will still have to do a full table scan on the first two tables. > The actual table structure is this: > CREATE TABLE event ( sid INT UNSIGNED NOT NULL, > cid INT UNSIGNED NOT NULL, > [...] > PRIMARY KEY (sid,cid), Each of the three tables has this. Now look at your SELECT and the join condition, purely on the field cid. So, MySQL actually has no index to work with. The (sid,cid) can't be used since cid is not the first field; you can use the first part of a key on its own, not the last part (since the last part will have no ordering independently the first part). So, you will want to add an index on cid alone, for the event and iphdr tables (tcphdr table doesn't matter since your select will use the tcp_flags index to limit down the first part of the join, as described above). Making sense? Please post the output from EXPLAIN if the above info does not have the desired effect. Then we can look at exactly what the server is thinking. And of course we also want to hear from you when it's ligning fast! > (it's the typical Snort database, and i don't want to change it, because > many applications related to Snort expect it to be this way) Adding an index shouldn't affect other apps, so that's easy. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php