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
