On Tue, 21 Aug 2001 18:55:15 +0400
Spirit <[EMAIL PROTECTED]> wrote:
> Hello!
>
> I need your suggestions on what keys to build for the table described
> below. Currently I have almost all possible keys on the table in order
> to see what keys mysql will use.
>
> I have a table here, which consist of over 6 million records by now
> and is defined as follows:
>
> CREATE TABLE traffic (
> PeerIP int(10) unsigned DEFAULT '0' NOT NULL,
> IP int(10) unsigned DEFAULT '0' NOT NULL,
> prefix tinyint(4) DEFAULT '0' NOT NULL,
> TrafficIn bigint(20) DEFAULT '0' NOT NULL,
> TrafficOut bigint(20) DEFAULT '0' NOT NULL,
> LinkID mediumint(8) unsigned DEFAULT '0' NOT NULL,
> time timestamp(14),
> PRIMARY KEY (time,LinkID,IP,PeerIP,prefix),
> KEY TimeIP (time,IP),
> KEY LnkID (LinkID),
> KEY LIP (LinkID,IP),
> KEY Time (time),
> KEY TLI (time,LinkID,IP)
> );
>
> A typical SELECT query issued on the table looks like this:
>
> select sum(TrafficIn), sum(TrafficOut)
> from traffic
> where time>? and time<? and LinkID in (...)
> and ((IP>=? and IP<? ) [or (IP>=? and IP<? ) [or...]]);
>
> Here is what I get from explain for different selects:
>
> explain select sum(TrafficIn), sum(TrafficOut)
> from traffic
> where time>20010801000000 and time<20010821235959
> and LinkID in (3,4,6,8) and ((IP>=0 and IP<4294967296 ));
>
>
>+---------+------+-----------------------------------+------+---------+------+---------+------------+
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
>
>+---------+------+-----------------------------------+------+---------+------+---------+------------+
> | traffic | ALL | TimeIP,LnkID,PRIMARY,LIP,Time,TLI | NULL | NULL | NULL |
>6585935 | where used |
>
>+---------+------+-----------------------------------+------+---------+------+---------+------------+
>
> explain select sum(TrafficIn), sum(TrafficOut)
> from traffic
> where time>20010801000000 and time<20010821235959
> and LinkID in (3) and ((IP>=3564062083 and IP<3564062084 )
> or (IP>=3564062016 and IP<3564062032 )
> or (IP>=3564062112 and IP<3564062128 )
> or (IP>=3564062136 and IP<3564062144 ));
>
>
>+---------+-------+-----------------------------------+------+---------+------+--------+-------+
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
>
>+---------+-------+-----------------------------------+------+---------+------+--------+-------+
> | traffic | range | TimeIP,LnkID,PRIMARY,LIP,Time,TLI | LIP | NULL | NULL |
>111775 | |
>
>+---------+-------+-----------------------------------+------+---------+------+--------+-------+
>
> Can anybody explain me why it doesn't use any keys (Time, for
> example) for the first select? What keys should I create to make
> MySQL always use some key and not go through all 6.5 (and fast
> increasing) million records each time?
>
> The version of MySQL currently used is 3.22.32
>
> Please answer directly to my e-mail address as I'm not subscribed to this
> mail-list.
>
> --
> Best regards,
> Spirit mailto:[EMAIL PROTECTED]
>
>
> ---------------------------------------------------------------------
> 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
Spirit,
Well, you don't have an index on IP solely so mysql has to fecth all rows anyhow,
that's why it's not using any keys at all. Same for your second select.
regards,
rene
---------------------------------------------------------------------
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
