It works on small tables fine. However when entries grow somewhere over 20 000 it stops (a rough estimate, did not test for the exact number). When I have for say 80 000 and have a select returning 2 000 entries it still doesn't work right. I don't quite understand the "cardinality" value, at least in the docs it says that it has to do with joins, but I do not perform any (one table only). Right now I don't have such a table loaded with data, so I can't try what kind of effect will ANALYZE have. Will try later on. Thanks though :)
On Mon, Oct 13, 2003 at 01:37:14AM -0500, Matt W wrote: > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
