Performance of database tag schemas
Philipp Keller has done some performance test on 4 different database schemas for storing tagged items. Although his article refers to tagging bookmarks, you can tag pretty much anything.
He tests a one table setup with and without a full text index (tags are just a column in the table), a two table setup (a tag table with a foreign key to the item), and a three table setup (tags, items, and tag item relation table).
I found the results to be somewhat surprising. The full text index is slowest on a small tag set (250 tags) but with (999) tags it was fastest for finding items tagged with two tags.
The three table method was faster than the two table method on all tests except for inserting new rows. The un-normalized one table method is obviously fastest for adding new items. I would guess it would be fastest for update and delete as well.
Note that his tests were done using MySQL, using a different database you might get different results.
Check out his report
- Insert Delayed with MySQL - August 2, 2005
- Related Posts - April 8, 2005
- Creating a Derby Datasource with ColdFusion Admin API - August 5, 2009
- Top 10 Reserved SQL Keywords - October 28, 2008
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
- Updating Java on ColdFusion or Lucee
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained