We're storing transactions in an accounting system using two tables, one contains the general description of the particular transaction (the other contains the entries, one for each account - which isn't relevant for the question).
Slightly edited, the first table looks something like this:
CREATE TABLE `transaction` ( `transaction_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `transaction_type` varchar(48) NOT NULL, `comment` text NOT NULL, `created` timestamp NOT NULL, PRIMARY KEY (`transaction_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The 'comment' field is only text, but occasionally contains interesting data, for example it could look like this: "remote_transaction_id=EN123121021 player_id=128912"
Since we're talking of 10s of millions of entries each month, searching using text-matching in the text isn't very efficient, and the text index would probably be pretty hideous as well.
A neat solution would be to put generic tags in a separate table with relevant indices:
CREATE TABLE `transaction_string_tag` ( `transaction_id` int(11) unsigned NOT NULL, `tag_id` int(11) unsigned NOT NULL, `tag_value` varchar(127) NOT NULL DEFAULT '', PRIMARY KEY (`transaction_id`,`tag_id`), KEY `transaction_id` (`transaction_id`), KEY `tag_id` (`tag_id`), KEY `tag_value` (`tag_value`,`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This tag table would grow pretty quickly.
Given that we're using MySQL 5.1.xx, what's our best option? Imagine that we're adding 3-4 tags per transaction.
EDIT: The tag tables will be only be written to once.
EDIT 2: Mistakenly wrote 100s instead of 10s. Last month had 12 million entries in the transaction table above (and the number of rows in the corresponding entry table is arount 36 million)