mysql - 同一张表上的多个内部联接

Question

我正在尝试在同一个表上进行多个连接，但在我添加了这么多之后，MYSQL 失去了连接。我知道我对这段代码做了太多事情并且阻塞了数据库。这是我正在使用的代码。我该如何简化它并使其更有效率？

SELECT 
  y.`value` AS `Year`,
  m.`value` AS `Make`,
  mo.`value` AS `Model`,
  t.`value` AS `Title`,
  se.`value` AS `Seller Email`,
  v.`value` AS `VIN`,
  p.`value` AS `Posted`,
  e.`value` AS `Expired`,
  d.`value` AS `Expire Date`
FROM
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='vehicleYear') AS y
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='sellerEmail') AS se
  USING (`record`)
  INNER JOIN
(SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='vehicleVin') AS v
  USING (`record`)
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='yearMakeModel') AS t
  USING (`record`)
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='vehicleadExpired') AS e
  USING (`record`)
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='vehicleModel') AS mo
  USING (`record`)
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='vehicleMake') AS m
  USING (`record`)
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='expireDate') AS d
  USING (`record`)
  INNER JOIN
  (SELECT `record`, `value` FROM thrn1_facileforms_subrecords WHERE `name`='datePosted') AS p
  USING (`record`)```

Answer 1

连接丢失很可能是因为客户端在等待结果等待时间过长(客户端超时)后正在关闭连接。你可以增加这个值，最终会得到答案。

这里真正的问题是“实体值属性”反模式的使用。您正在使用不同的行来存储不同类型的数据，因此必须运行此 mega-join 才能获得您想要的数据。相反，您应该尝试将给定记录的所有数据放在一行中，并根据需要包含尽可能多的列。

我考虑使用 EVA 模式的唯一原因是当应用程序允许用户定义“自定义字段”时。是的，太多这样的字段会产生极其低效的查询。