I have a watchlist table containing almost 3Mil entries today.
mysql> select count(*) from watchlist;
+----------+
| count(*) |
+----------+
| 2957994 |
+----------+
It is used as a magazine for recording product page views on a large e-commerce site (50,000+ products). It records the product identifier of the product being viewed, the IP address and USER_AGENT of the viewer. And the timestamp when this happens:
mysql> show columns from watchlist;
+-----------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| productID | int(11) | NO | MUL | 0 | |
| ip | varchar(16) | YES | | NULL | |
| added_on | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
| agent | varchar(220) | YES | MUL | NULL | |
+-----------+--------------+------+-----+-------------------+-------+
(, , GoogleBot), (, " " , , " " ..).
"" , :
mysql> show indexes from watchlist;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| watchlist | 1 | added_on | 1 | added_on | A | NULL | NULL | NULL | | BTREE | |
| watchlist | 1 | productID | 1 | productID | A | NULL | NULL | NULL | | BTREE | |
| watchlist | 1 | agent | 1 | agent | A | NULL | NULL | NULL | YES | BTREE | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
INDEXES , , 30-45 , 7 . < 0,2.
, INDEXES , 5 . , mysqld , 10-15% ( 2 ). , 100% mysqld.
. INSERT, - SELECT. INSERT, 1000 TRIGGER, 900 SELECT. ( ) ( ), , , , (.. 1000 ). .
: ?
: TRIGGERS MySQL nice trigger_statement, , CPU? cron 30 , , , , ?