Dedicated SQL table containing only unique strings

Question

I can't seem to find any examples of anyone doing this on the web, so am wondering if maybe there's a reason for that (or maybe I haven't used the right search terms). There might even already be a term for this that I'm unaware of?

To save on database storage space for regularly reoccurring strings, I'm thinking of creating a MySQL table called unique_string. It would only have two columns:

1. "id" : INT : PRIMARY_KEY index
2. "string" : varchar(255) : UNIQUE index

Any other tables anywhere in the database can then use INT columns instead of VARCHAR columns. For example a varchar field called browser would instead be an INT field called browser_unique_string_id.

I would not use this for anything where performance matters. In this case I'm using it to track details of every single page request (logging web stats) and an "audit trial" of user actions on intranets, but other things potentially too.

I'm also aware the SELECT queries would be complex, so I'm not worried about that. I'll most likely write some code to generate the queries to return the "real" string data.

Thoughts? I feel like I might be overlooking something obvious here.

Thanks!

Answer 1

I have used this structure for a similar application -- keeping track of URIs for web logs. In this case, the database was Oracle.

The performance issues are not minimal. As the database grows, there are tens of millions of URIs. So, just identifying the right string during an INSERT is challenging. We handled this by building most of the update logic in hadoop, so the database table was, in essence, just a copy of a hadoop table.

In a regular database, you would get around this by building an index, as you suggest in your question. And, an index solution would work well up to your available memory. In fact, this is a rather degenerate case for an index, because you really only need the index and not the underlying table. I'm do not know if mysql or SQL Server recognize this, although columnar databases (such as Vertica) should.

SQL Server has another option. If you declare the string as VARCHAR(max), then it is stored no a separate data page from the rest of the data. During a full table scan, there is no need to load the additional page in memory, if the column is not being referenced in the query.

Answer 2

This is a very common design pattern in databases where the cardinality of the data is relatively small compared to the transaction table that it's linked to. The queries wouldn't be very complex, just a simple join to the lookup table. You can include more than just a string on the lookup table, other information that is commonly repeated. You're simply normalizing your model to remove duplicate data.

Example:

Request Table:

Date    
Time   
IP Address    
Browser_ID  

Browser Table:

Browser_ID
Browser_Name
Browser_Version
Browser_Properties

Answer 3

If you planning on logging data in real time (as opposed to a batch job) then you want to ensure your time to write a record to the database is as quick as possible. If you are logging synchronously then obviously the record creating time will directly affect the time it takes for a http request to complete. If this is async then slow record creation times will lead to a bottleneck. However if this is batch job then performance will not matter so long as you can confidently create all the batched records before the next batch runs.

In order to reduce the time it takes to create a record you really want to flatten out your database structure, your current query in pseudo might look like

SELECT @id = id from PagesTable
WHERE PageName = @RequestedPageName

IF @id = 0
THEN 
  INSERT @RequestedPageName into PagesTable
  @id = SELECT @@IDENTITY 'or whatever method you db supports for              
                          'fetching the id for a newly created record
END IF

INSERT @id, @BrowserName INTO BrowersLogTable 

Where as in a flat structure you would just need 1 INSERT

If you are concerned about data Integrity, which you should be, then typically you would normalise this data by querying at writing it into a separate set of tables (or a separate database) at regular intervals and use this for querying against.