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]
