Hi Peter, I just tried it on a table with 10 rows and it works fine on 4.0.15. Have you already tried on a small table?
How many rows is your WHERE clause matching when it's not using the index? Do you know that MySQL won't use an index if it thinks it will find more than about 30% of all rows? What happens after you run ANALYZE TABLE? It doesn't appear that you've done so since your index cardinality is NULL. Matt ----- Original Message ----- From: "Peter Rabbitson" Sent: Sunday, October 12, 2003 10:42 PM Subject: Re: unexpected index behaviour... > 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]
