Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
If you're expecting 100M records per month you'll exhaust your keyspace in 3-4 years. If you plan on being around that long, use a bigint for auto inc. At the same time you'll run into other problems if you end up having 4B+ rows in a single table. –  atxdba Jan 27 at 19:19
    
@atxdba Sorry, 100M was wrong, as you see I corrected it. 12M was last month and I expect we'll start archiving soon to keep 4-6 months' worth of (non-archived) data available in the live db. –  Nuoji Jan 27 at 20:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.