如何在垂直设计的表上实现搜索?

EAV 只要不需要同时搜索多个值,表就是一件好事,在这种情况下,它就变成了一件坏事。

不能同时索引多个值,因为它们位于不同的记录中。

在一个 SQL Server 表可以在多个值上创建索引视图,并将其用于搜索。

在 Oracle ,您可以通过 UserID 所有的记录都是一样的 用户标识 在一个数据页中,它将使用对最有选择的值的索引,并快速扫描其他值。

在 PostgreSQL ,可以将所有值存储在单个数组中,并使用 GIN 索引。

在 MySQL 你不能这样做。

下面是一个将返回值的查询:

SELECT  *
FROM    tbl_UserAttributes tcity
JOIN    tbl_UserAttributes tsex
ON      tsex.userid = tcity.userid
WHERE   tcity.fieldname = 'city'
        AND tcity.value LIKE '%Mumbai%'
        AND tsex.fieldname = 'sex'
        AND tsex.value = 'M'

但别指望速度会很快。

更新:

如果需要精确匹配,可以在 (fieldname, value, userid) 把最有选择性的 fieldname 放在第一张桌子上使用 STRAIGHT_JOIN 强制执行命令:

SELECT  *
FROM    tbl_UserAttributes tcity
STRAIGHT_JOIN
        tbl_UserAttributes tsex
ON      tsex.userid = tcity.userid
WHERE   tcity.fieldname = 'city'
        AND tcity.value = 'Mumbai'
        AND tsex.fieldname = 'sex'
        AND tsex.value = 'M'

但是,这对您当前的查询没有帮助,因为您正在寻找一个通配符匹配,在这种情况下,索引不是很有用。第二个表不会从索引中受益太多,除非您在查询妇产医院数据库。

不过,由于可以使用索引扫描而不是表扫描,因此它将为您节省一些时间。

以前见过这个。不要搜索与城市、性别等匹配的内容,而是计算与搜索查询匹配的属性数量。如果此计数等于搜索查询中的属性数,则为结果之一。

是否有固定的域名集?

如果可以的话,我建议设置一个视图,使其水平并便于查询。在SQL Server 2005中,它类似于:

SELECT *
FROM
(SELECT [UserID], [FieldName], [Value]
FROM [tbl_UserAttributes] ) ps
PIVOT
(
MAX([Value])
FOR [FieldName] IN
( [City], [Phone], [sex], [firstname])
) AS pvt

尽管所有必需的[FieldName]值都需要位于in()部分中,才能为每个值拉出一个字段,但这应该使其水平。另外,使用max意味着如果同一个字段名有多个值,它将提取max值。

对于那些提供帮助的人来说,这是EAV(实体属性值)的经典案例。在设计应用程序时,强烈建议不要这样做。

您必须在userid=userid的同一个表中加入,显然加入的一边是“where fieldname='city'and value='houston'”,另一边是“where fieldname='sex'and value='m'”。希望不要同时搜索太多不同的字段!

奎斯诺以30秒击败了我。

select ua.userID
from tblUserAttributes ua
INNER JOIN tblUserAttributes ua2
ON ua.userID = ua2.userID
and ua2.firstname = 'john'
INNER JOIN tblUserAttributes ua3
ON ua.userID = ua3.userID
and ua3.lastname = 'smith'
where ua.sex = 'M'

如果前面提到的内部连接方法不可接受,那么我建议您每隔x分钟将表聚合为水平格式,然后使用新表作为搜索的基础。

我认为与其尝试将其作为一个缓慢的查询来完成,不如将其作为几个快速查询来完成,每个条件对应一个查询。也许是这样。

CREATE TEMPORARY table search_results (User_id,score)
  SELECT User_id, 1 FROM tbl_UserAttributes
    WHERE FieldName ='blah' and FieldValue='x'; //should put an index on search_results.User_id

UPDATE search_results s JOIN tbl_UserAttributes u USING (User_id)
SET s.score=s.score+1 WHERE u.FieldName ='foo' and FieldValue='y';

无论有多少情况,都要重复更新。

SELECT User_id FROM search_results WHERE score= 'number of conditions'.

上面的选择可以连接到tbl_userattributes以输出所需的字段名。

也许不建议这样做,但我知道它是从哪里来的。作为灵活性的交换,您在颈部查询中会遇到一些困难。

要查询两个属性,您需要一个联接。

select a1.userid from tbl_UserAttributes a1, tbl_UserAttributes a2 where
a1.userid=a2.userid 
and a1.FieldName='city' and a1.Value='Mumbai'
and a2.FieldName='sex' and a2.Value='M'

很快就会变得笨拙。

Upd:

正如布莱恩所说,你最好数数比赛的次数。

select userid, count(*) from tbl_UserAttributes 
where (FieldName='city' and a1.Value='Mumbai')
or (FieldName='sex' and a2.Value='M')
group by userid
having count(*)=2

这个应该工作得更快