Geeee you guys are very helpful... Anyway posting a reply to my own question. There is 
no way to use a timestamp as an 
index in a relatively large table. MySQL just does not take it even with explicit 
FORCE KEY. Why? - I have no idea. 
A workaround I found for this is running a script every minute that transfers data 
from the main table to a secondary table, 
which in place of the time stamp field has an integer field (in my case I truncate the 
timestamp field with 
right(pkt_time,6) and use a MEDIUMINT to store tha value of hours minutes and 
seconds). Only then I can index the field in 
the new table, and although it still doesn't work natively, a FORCE KEY correctly 
selects and uses the corresponding index. 
Downside is I lose 1 minute of data but... what gives. 

Cheers

Peter



On Fri, Oct 03, 2003 at 04:13:51AM -0500, Peter Rabbitson wrote:
> Hello all, 
> I asked a similar question earlier. Then I went and did tons of tests... but I am 
> back where I started. Basicly I am trying 
> to store pretty high volume of data (ip traffic) in a mysql database. The only 
> choidce for an engine is MyIsam because I 
> need the advantage of compressed tables. Also I need to be able to use indexes to 
> optimize my queries in a table with > 200 
> 000 entries. The problem begins when a table that includes a timestamp() column 
> grows over a given size any select from the 
> table stops using the indexes. First, as the table gets filled with data, queries 
> based on a "where" from the timestamp 
> column refuse to use the index, then when the table grows even larger - all other 
> indexes fail as well. 
> >From what I've read it seems that mysql has a system of determining when to use an 
> >index and when to fall back to reading 
> the whole table. But i never found any documentation on how to control this 
> mechanism. Playing with the key_buffer doesn't 
> yield any results. Could you please point me to any information about an issue like 
> this. Thanks
> 
> Peter
> 
> Below are some excerpts to make the picture brighter:
> 
> mysql> describe ulog;
> +------------+----------------------+------+-----+---------+-------+
> | Field      | Type                 | Null | Key | Default | Extra |
> +------------+----------------------+------+-----+---------+-------+
> | pkt_time   | timestamp(12)        | YES  | MUL | NULL    |       |
> | oob_prefix | varchar(32)          | YES  | MUL | NULL    |       |
> | oob_mark   | int(10) unsigned     | YES  |     | NULL    |       |
> | oob_in     | varchar(32)          | YES  |     | NULL    |       |
> | oob_out    | varchar(32)          | YES  |     | NULL    |       |
> | ip_saddr   | int(10) unsigned     | YES  |     | NULL    |       |
> | ip_daddr   | int(10) unsigned     | YES  |     | NULL    |       |
> | ip_tos     | tinyint(3) unsigned  | YES  |     | NULL    |       |
> | ip_ttl     | tinyint(3) unsigned  | YES  |     | NULL    |       |
> | ip_totlen  | smallint(5) unsigned | YES  |     | NULL    |       |
> | udp_sport  | smallint(5) unsigned | YES  |     | NULL    |       |
> | udp_dport  | smallint(5) unsigned | YES  |     | NULL    |       |
> +------------+----------------------+------+-----+---------+-------+
> 12 rows in set (0.00 sec)
> 
> mysql> select * from ulog limit 300,1;
> +--------------+------------+----------+--------+---------+------------+------------+--------+--------+-----------+-----------+-----------+
> | pkt_time     | oob_prefix | oob_mark | oob_in | oob_out | ip_saddr   | ip_daddr   
> | ip_tos | ip_ttl | ip_totlen | 
> udp_sport | udp_dport |
> +--------------+------------+----------+--------+---------+------------+------------+--------+--------+-----------+-----------+-----------+
> | 031003023231 | fwin3      |        0 | eth0   | eth2    | 1079095811 | 3232235779 
> |      0 |    112 |       170 |     
> 27016 |
>  1817 |
> +--------------+------------+----------+--------+---------+------------+------------+--------+--------+-----------+-----------+-----------+
> 1 row in set (0.00 sec)
> 
> 
> mysql> show index from ulog;
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | 
> Cardinality | Sub_part | Packed | Null | 
> Index_type | Comment |
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | ulog  |          1 | glbl     |            1 | pkt_time    | A         |        
> NULL |     NULL | NULL   |      | BTREE      
> |
>      |
> | ulog  |          1 | glbl     |            2 | oob_prefix  | A         |        
> NULL |     NULL | NULL   | YES  | BTREE      
> |
>      |
> | ulog  |          1 | prfx     |            1 | oob_prefix  | A         |        
> NULL |     NULL | NULL   | YES  | BTREE      
> |
>      |
> | ulog  |          1 | tim      |            1 | pkt_time    | A         |        
> NULL |     NULL | NULL   |      | BTREE      
> |
>      |
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> 4 rows in set (0.00 sec)
> 
> mysql> explain select * from ulog where pkt_time = 031003023231;
> +-------+------+---------------+------+---------+------+-------+-------------+
> | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
> +-------+------+---------------+------+---------+------+-------+-------------+
> | ulog  | ALL  | glbl,tim      | NULL |    NULL | NULL | 15323 | Using where |
> +-------+------+---------------+------+---------+------+-------+-------------+
> 1 row in set (0.00 sec)
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to