Mysql – Multiple columns (one per type) vs. single TEXT/Clob column

databaseMySQLperformancesql

I need to store some diffs of other tables' columns in the database. I want to use a single table.

Option A

Table with 4 columns:

id
content_type (VARCHAR 255, will be: datetime, integer, varchar, text/clob …)
old_content (CLOB / TEXT, software serialized data for content-type)
new_content (CLOB / TEXT, software serialized data for content-type)

Option B

Table with ~10+ columns:

id
content_type (VARCHAR 255)
old_datetime (DATETIME)
new_datetime (DATETIME)
old_integer (INTEGER)
new_integer (INTEGER)
old_string (VARCHAR 255)
new_string (VARCHAR 255)
old_text (CLOB / TEXT)
new_text (CLOB / TEXT)
…

Only one tuple will be set at a time, the others will be NULL.

Evaluation

At the moment I would prefer Option B – because I'm not a huge fan of serialized data in a RDBMS. However I don't know whether databases (in my case MySQL, maybe Postgre) would be better at handling (compressing) Option A in contrast to Option B.

I have seen approach B in some other projects (JBoss jBPM) and also feel more comfortable with B, but I'm ready to be lectured.

Would you use A or B and why? What are the tradeoffs of each option? Or is there maybe a C?

Other stuff that might be relevant:

For the current project, I assume that there will be more data in the non-TEXT/CLOB columns.
I do not want to search through the non-ID columns, so no additional Index is needed.

Best Answer

Well, there is one more solution, more tables with supertype/subtype structure. The good thing about this one is that there are no extra nulls, for example:

TABLE DataChange (ID (PK), content_type, ChangeTime)
TABLE IntChange (ID (PK=FK), old_value int, new_value int)
TABLE StrChange (ID (PK=FK), old_value varchar(255), new_value varchar(255))
TABLE TxtChange (ID (PK=FK), old_value text, new_value text)
...

IntChange, StrChange, and TxtChange are types of DataChange, so ID serves as the primary and the foreign key and is the same number as the ID in the DataChange table. The content_type is the classifier column. Here is a model of a similar (supertype/subtype) example.

Related Solutions

Sql-server – Multiple Indexes vs Multi-Column Indexes

I agree with Cade Roux.

This article should get you on the right track:

One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column. Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits.

Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

you sometimes search by state only.
you sometimes search by state and county.
you frequently search by state, county, zip.

Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used (by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

By the way We can take advantage of the fact that with Multi-Column indexing the first index column is always usable for searching and when you search only by 'state' it is efficient but yet not as efficient as Single-Column index on 'state'

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

The article will help a lot. :-)

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2