作为关系表检索MySQL EAV结果的最佳性能是什么？ [英] What is best performance for Retrieving MySQL EAV results as Relational Table

问题描述

我想从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

Get all Entities with last name like 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.

选项：

1. 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?

相关问题：

1. 绑定EAV结果


2. 如何转载MySQL实体

1. Binding EAV results
2. 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结果的最佳性能是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！