Flattened Tables vs. CSV Columns: Which Database Design Offers Better Query Performance?

Database Optimization: Flattened Tables vs. CSV Columns

When designing a database, the decision between storing data in a single column with a comma-separated value (CSV) or using a flattened table with a row for each entry always arises. This article delves into the performance implications of these approaches, focusing on queries that involve filtering or searching.

Consider a SQL table with rows of the form:

value, "a,b,c,d,e,f,g,h,i,j", value3, value4

where queries search for pairs like value, %b% using the LIKE operator.

In contrast, a flattened table would have each permutation as a separate row:

value, a, value3, value4
...
value, j, value3, value4

allowing queries to use the = operator on value,b.

For this specific use case, with approximately 10,000 rows and an average of 8 entries per "list" element, the performance implications are significant.

LIKE queries cannot leverage indexes, resulting in slower lookups. Additionally, storing data in a single CSV column is an anti-pattern that can hinder database performance.

Breaking up the CSV column into separate columns and normalizing the database would significantly improve efficiency. By doing so, indexes can be utilized, and the LIKE operator can be replaced with the = operator, leading to faster queries.

The above is the detailed content of Flattened Tables vs. CSV Columns: Which Database Design Offers Better Query Performance?. For more information, please follow other related articles on the PHP Chinese website!