mysql - SQL table with "list" entry vs SQL table with a row for each entry

Question

I have a Sqlite table where each row is the form of:

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

Where my queries look for a pairing of value, %b% using LIKE. Is it more efficient in terms of query time to instead have a table where each row is a permutation:

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

So that my queries are now value,b using the = operator this time.

As a note, the use case I'm interested in has ~10,000 rows currently where each "list" element has on average 8 entries. I tagged both Sqlite and MySQL because I have a similar problem for both a db with MySQL and Sqlite.

Answer 1

Where my queries look for a pairing of value, %b% using LIKE. Is it more efficient in terms of query time to instead have a table where each row is a permutation:

Most definitely. Because LIKE '%something%' type queries cannot use indexes. So you look ups are going to be very slow. If that's not enough you are using pretty much an RDBMS anti pattern. more details here: Is storing a delimited list in a database column really that bad?

once you break up your 'CSV' column into separate columns, you can take it still further by normalizing the database.