问题描述
我想从EAV(实体属性值)表中提取结果,或更具体的实体元数据表(如wordpress wp_posts
和 wp_postmeta
)作为一个格式很好的关系表,为了做一些排序和/或过滤。
I want to extract results from EAV (entity-attribute-value) tables, or more specifically entity-metadata tables (think like wordpress wp_posts
and wp_postmeta
) as a "nicely formatted relational table", in order to do some sorting and/or filtering.
我已经找到了一些如何在查询中格式化结果的示例(而不是编写2个查询并加入代码中的结果),但是我想知道这样做的最有效的方法,特别是较大的结果集。
I've found some examples of how to format the results within the query (as opposed to writing 2 queries and joining the results in code), but I would like to know the "most efficient" method for doing so, especially for larger result sets.
当我说最有效率时,我的意思是像以下场景:
And when I say "most efficient", I mean for something like the following scenarios:
获取所有具有姓名的实体,如XYZ
返回按生日排序的实体列表
Return a list of Entities sorted by birthday
eg转过来:
** ENTITY **
-----------------------
ID | NAME | whatever
-----------------------
1 | bob | etc
2 | jane | etc
3 | tom | etc
** META **
------------------------------------
ID | EntityID | KEY | VALUE
------------------------------------
1 | 1 | first name | Bob
2 | 1 | last name | Bobson
3 | 1 | birthday | 1983-10-10
. | 2 | first name | Jane
. | 2 | last name | Janesdotter
. | 2 | birthday | 1983-08-10
. | 3 | first name | Tom
. | 3 | last name | Tomson
. | 3 | birthday | 1980-08-10
到这个:
** RESULTS **
-----------------------------------------------
EID | NAME | first name | last name | birthday
-----------------------------------------------
1 | bob | Bob | Bobson | 1983-10-10
2 | jane | Jane | Janesdotter | 1983-08-10
3 | tom | Tom | Tomson | 1980-08-10
所以我可以通过任何元字段进行排序或过滤。
so I can sort or filter by any of the meta fields.
我发现了一些建议,但是我找不到有哪些更好的讨论。
I found some suggestions here, but I can't find any discussion of which performs better.
选项:
- GROUP_CONCAT :
SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' )
FROM ENTITY
e JOIN META
m ON e.ID = m.EntityID
SELECT e.*, m1.VALUE as 'first name', m2.VALUE as 'last name', m3.VALUE as 'birthday'
FROM ENTITY
e
LEFT JOIN META
m1
ON e.ID = m1.EntityID AND m1.meta_key = 'first name'
LEFT JOIN META
m2
ON e.ID = m2.EntityID AND m2.meta_key = 'last name'
LEFT JOIN META
m3
ON e.ID = m3.EntityID AND m3.meta_key = 'birthday'
SELECT e.*
, MAX( IF(m.KEY= 'first name', m.VALUE, NULL) ) as 'first name'
, MAX( IF(m.KEY= 'last name', m.VALUE, NULL) ) as 'last name'
, MAX( IF(m.KEY= 'birthday', m.VALUE, NULL) ) as 'birthday'
FROM ENTITY
e
JOIN META
m
ON e.ID = m.EntityID
SELECT e.* FROM ENTITY
e WHERE e.ID = {whatever};
在PHP中,从结果创建一个占位符对象
SELECT m.* FROM META
m WHERE m.EntityID = {whatever};
循环结果并附加到实体对象,如:
$ e - > {$ result-> key} = $ result-> VALUE
哪一个更好一些,过滤/排序?
Which is better in general, and for filtering/sorting?
相关问题:
- Binding EAV results
- How to Pivot a MySQL entity
推荐答案
使用枢轴或聚合的任何内容可能会更快,因为它们不需要表要自联。基于连接的方法将需要优化器执行几个子查询操作,然后将结果加在一起。对于一个小数据集,这可能并不重要,但如果您在较大的数据集上进行分析查询,则可能会显着降低性能,
Anything using pivot or aggregates will probably be faster, as they don't require the table to be self-joined. The join based approaches will require the optimiser to perform several sub-query operations and then join the results together. For a small data set this might not matter so much, but this could significantly degrade performance if you're doing an analytic query on a larger data set,
这篇关于作为关系表检索MySQL EAV结果的最佳性能是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!