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

Reply via email to