CODEWITHSUNDEEP
mysqlsqlsqlperformance
Toleo
Toleo

Reputation: 774

Which of those Tables Schema is Best for Performance (Columns vs. Rows)?

I have to go with one of two of the Table Schemas,

First Schema:

SELECT * 
FROM `posts` 
WHERE `id` = '1'
     ----
id  title   content     col1    col2    col3
1   Title1  Content1    A       B       C

Second Schema:

SELECT p.*, GROUP_CONCAT(c.`col` ORDER BY `col_number`) AS cols 
FROM `posts` p
LEFT JOIN `cols` c ON c.`pid` = p.`id`
WHERE p.`id` = 1;
     ----
id  title   content     cols
1   Title1  Content1    A,B,C

In both cases, I'm limited to only three cols for each record in post table, And will collect them like an array as in Second Schema A,B,C

Does it matter which schema is used on Performance?

What I know is:

First Schema can have NULL on col2, col3.

Second Schema will have no NULL cols unless Some of the columns were Updated to NULL after having a value before Except col_number = 1's ROW.

In both cases they both will have NULL cols values, the Second Schema NULL cols can be DELETED, But I don't know If it is better to keep it or DELETE them.

Briefly: Which Schema is Better for Performance? Is it better to keep NULL col in Second Schema or DELETE them?

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You should never store lists of things as delimited strings. Period. You can store them as JSON or XML or using some other method, but storing multiple values in a string is a bad idea.

Hence, the first method is the better method. In terms of performance, the two should be pretty similar for basic queries. The first allows you to create indexes on each column, which can be helpful for some purposes.

The space usage is pretty similar. You are talking about length bytes for strings versus delimiters.

But for relational modeling, there is no question. Separate columns -- or even separate rows in an association table -- are the right solution.

Upvotes: 1

Related Questions