将 MySQL EAV 结果作为关系表检索的最佳性能是什么 [英] What is best performance for Retrieving MySQL EAV results as Relational Table
问题描述
我想从 EAV(实体-属性-值)表中提取结果，或者更具体地说是实体-元数据表(像 wordpress wp_posts 和 wp_postmeta)作为格式良好的关系表"，为了进行一些排序和/或过滤.
我找到了一些关于如何在查询中格式化结果的示例(而不是编写 2 个查询并在代码中加入结果)，但我想知道这样做的最有效"方法，尤其是用于更大的结果集.
当我说最有效"时，我的意思是类似以下场景:
<块引用>获取姓氏为 XYZ 的所有实体
返回按生日排序的实体列表
<小时>
例如转这个:
<前>** 实体 **-----------------------身份证 |姓名 |任何-----------------------1 |鲍勃 |等等2 |简|等等3 |汤姆 |等等** 元 **---------------------身份证 |实体 ID |钥匙 |价值---------------------1 |1 |名字 |鲍勃2 |1 |姓氏 |鲍森3 |1 |生日 |1983-10-10.|2 |名字 |简.|2 |姓氏 |简斯多特.|2 |生日 |1983-08-10.|3 |名字 |汤姆.|3 |姓氏 |汤臣.|3 |生日 |1980-08-10进入这个:
<前>** 结果 **-----------------------------------------------开斋节 |姓名 |名字 |姓氏 |生日-----------------------------------------------1 |鲍勃 |鲍勃 |博森 |1983-10-102 |简|简|简斯多特 |1983-08-103 |汤姆 |汤姆 |汤臣 |1980-08-10所以我可以按任何元字段排序或过滤.
<小时>我发现了一些建议 here，但我找不到任何关于哪个表现更好的讨论.
选项:
- GROUP_CONCAT:<前>SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' )从`ENTITY` e JOIN `META` m ON e.ID = m.EntityID
- 多连接:<前>SELECT e.*, m1.VALUE 为名字"，m2.VALUE 为姓氏"，m3.VALUE 为生日"从`实体` e左加入`META` m1ON e.ID = m1.EntityID AND m1.meta_key = '名字'左加入`META` m2ON e.ID = m2.EntityID AND m2.meta_key = 'last name'左加入`META` m3ON e.ID = m3.EntityID AND m3.meta_key = '生日'
- 合并:<前>选择 e.*, MAX( IF(m.KEY= 'first name', m.VALUE, NULL) ) 为 'first name', MAX( IF(m.KEY= 'last name', m.VALUE, NULL) ) 为 'last name', MAX( IF(m.KEY= '生日', m.VALUE, NULL) ) 为 '生日'从`实体` e加入`META`米ON e.ID = m.EntityID
- 代码:<前>SELECT e.* FROM `ENTITY` e WHERE e.ID = {whatever};
在 PHP 中，从结果创建一个占位符对象<前>SELECT m.* FROM `META` m WHERE m.EntityID = {whatever};
在 PHP 中，遍历结果并附加到实体对象，例如:
$e->{$result->key} = $result->VALUE
一般来说哪个更好，用于过滤/排序?
相关问题:
任何使用数据透视或聚合的方法可能会更快，因为它们不需要自联接表.基于连接的方法将要求优化器执行多个子查询操作，然后将结果连接在一起.对于小数据集，这可能无关紧要，但如果您对更大的数据集进行分析查询，这可能会显着降低性能，
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.
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:
Get all Entities with last name like XYZ
Return a list of Entities sorted by birthday
e.g. turn this:
** 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
into this:
** 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.
Options:
- 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 - Multi-Join:
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' - Coalescing:
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 - Code:
SELECT e.* FROM `ENTITY` e WHERE e.ID = {whatever};in PHP, create a placeholder object from result
SELECT m.* FROM `META` m WHERE m.EntityID = {whatever};in PHP, loop through results and attach to entity object like:
$e->{$result->key} = $result->VALUE
Which is better in general, and for filtering/sorting?
Related questions:
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屋！
