MySQL 通过优化选择不同或组

Question

我在从查询中获取唯一结果时遇到问题。详细信息表有 800K 行，矩阵表有 988K 行。有索引，但要么我没有设置正确的索引，要么它们不能与查询一起使用。解释说详细表没有可能的键。矩阵显示可能的键，但键值为 NULL，Extra 表示:

Using where; Using join buffer (flat, BNL join)

查询:

SELECT
  Detail.Geocode AS Geocode,
  Detail.Taxtype AS Taxtype,
  Detail.TaxCat AS TaxCat,
  Detail.TaxAuthId AS TaxAuthId,
  Detail.TaxRate AS TaxRate,
  Detail.Fee AS Fee,
  Matrix.`Group` AS `Group`,
  Matrix.Item AS Item,
  Matrix.Customer AS Customer
FROM Detail
  LEFT OUTER JOIN Matrix
    ON Detail.Taxtype = Matrix.TaxType
    AND Detail.TaxCat = Matrix.TaxCat
    AND Detail.Geocode = Matrix.Geocode
    OR Matrix.Geocode = CONCAT(SUBSTR(Detail.Geocode, 1, 4), '00000000')
    AND Matrix.TaxAuthId = Detail.TaxAuthId
    OR ISNULL(Matrix.TaxAuthId)

执行上面的查询可以正常完成，但会产生大量重复项。鉴于我正在使用的数据，我预计会出现这种情况。

我尝试过使用 DISTINCT 和 GROUP BY 但它从未完成。

例如

GROUP BY Detail.Geocode,
         Detail.Taxtype,
         Detail.TaxCat,
         Detail.TaxAuthId,
         Detail.TaxRate,
         Detail.Fee,
         Matrix.`Group`,
         Matrix.Item,
         Matrix.Customer

我见过一些将 DISTINCT 放置在联接内的示例，但我无法弄清楚如何使用我的查询正确执行此操作。

Answer 1

问题出在 () 上。谢谢 xQbert!

SELECT
  Detail.Geocode AS Geocode,
  Detail.Taxtype AS Taxtype,
  Detail.TaxCat AS TaxCat,
  Detail.TaxAuthId AS TaxAuthId,
  Detail.TaxRate AS TaxRate,
  Detail.Fee AS Fee,
  Matrix.`Group` AS `Group`,
  Matrix.Item AS Item,
  Matrix.Customer AS Customer
FROM Detail
  LEFT OUTER JOIN Matrix
    ON Detail.Taxtype = Matrix.TaxType
    AND Detail.TaxCat = Matrix.TaxCat
    AND (Detail.Geocode = Matrix.Geocode
    OR Matrix.Geocode = CONCAT(SUBSTR(Detail.Geocode, 1, 4), '00000000'))
    AND (Matrix.TaxAuthId = Detail.TaxAuthId
    OR ISNULL(Matrix.TaxAuthId))