带“列表"的SQL表条目与SQL表的比较，每个条目都有一行 [英] SQL table with "list" entry vs SQL table with a row for each entry

问题描述

我有一个Sqlite表，其中每一行都是以下形式:

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

我的查询在其中使用LIKE查找值的配对，%b%.就查询时间而言，改为使用一个表格(其中每一行都是一个排列)是否更有效:

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

这次我的查询现在是值，b使用=运算符.

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

请注意，我感兴趣的用例当前有大约10,000行，其中每个列表"元素平均有8个条目.我同时标记了Sqlite和MySQL，因为对于具有MySQL和Sqlite的数据库，我都遇到了类似的问题.

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.

推荐答案

在我的查询中，使用LIKE查找值的配对，％b％.是吗 在查询时间方面更高效，而是有一个表 每行是一个排列:

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:

多数是肯定的.因为喜欢'％something％'类型的查询无法使用索引.因此，您的查找将会非常缓慢.如果这还不够，那么您将使用RDBMS反模式.此处有更多详细信息:正在存储数据库列中的分隔列表真的那么糟糕吗?

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?

一旦您将"CSV"列拆分为单独的列，就可以通过规范化数据库来进一步扩展它.

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

这篇关于带“列表"的SQL表条目与SQL表的比较，每个条目都有一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！