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]