如何使用 Sphinx 搜索大型 JOINed 表？

Question

我的数据库中有几个不同的表，我正在尝试使用 Sphinx 进行快速全文搜索。为了便于讨论，我们假设感兴趣的主要记录是装箱单，订单发货时会包含其中之一。如何使用 Sphinx 在所有这些表中执行复杂查询而不完全对数据库进行非规范化？

每张装箱单都列出了订单号、发件人、收件人以及货件中每个箱子的跟踪号码。单独的表包含有关订单项目的信息。附加表包含客户地址信息。因此，订单包含盒子，盒子包含物品。 （本问题底部列出的示例架构）。

我希望能够向 Sphinx 查询以下问题的答案：

• 居住在名为“Maple”的街道上的有多少人订购了以下商品：描述中的“大”？
• 哪些订单的包装盒描述或订单商品描述中包含“蓝色”一词？

为了回答这类问题，我需要参考几个表格。由于 Sphinx 没有 JOIN，因此一种选择是对数据库进行非规范化。使用视图进行非规范化，以便每一行代表一个订单项，加上其父框和订单的所有数据，将导致数十亿个非常宽的行。所以我一直为每个表创建一个单独的索引。但这不允许我像 SQL JOIN 那样跨表查询。还有其他解决方案吗？

示例数据库

CREATE TABLE orders (
    id               integer PRIMARY KEY,
    date_ordered     date,
    customer_po      varchar
);
INSERT INTO orders VALUES (1, '2012-12-13', NULL);
INSERT INTO orders VALUES (2, '2012-12-14', 'DF312442');

CREATE TABLE parties (
    id               integer PRIMARY KEY,
    order_id         integer NOT NULL REFERENCES orders(id),
    party_type       varchar,
    company          varchar,
    city             varchar,
    state            char(2)
);
INSERT INTO parties VALUES (1, 1, 'shipper', 'ACME, Inc.', 'New York', 'NY');
INSERT INTO parties VALUES (2, 1, 'recipient', 'Wylie Coyote Corp.', 'Flagstaff', 'AZ');
INSERT INTO parties VALUES (3, 2, 'shipper', 'Cyberdyne', 'Las Vegas', 'NV');
-- Please disregard the fact that this design permits multiple shippers and multiple recipients
-- per order.  This is a vastly simplified version of the system I'm working on.

CREATE TABLE boxes (
    id               integer PRIMARY KEY,
    order_id         integer NOT NULL REFERENCES orders(id),
    tracking_num     varchar NOT NULL,
    description      varchar NOT NULL,
);
INSERT INTO boxes VALUES (1, 1, '1234567890', 'household goods');
INSERT INTO boxes VALUES (2, 1, '0987654321', 'kitchen appliances');
INSERT INTO boxes VALUES (3, 2, 'ABCDE12345', 'audio equipment');

CREATE TABLE box_contents (
    id               integer PRIMARY KEY,
    order_id         integer NOT NULL REFERENCES orders(id),
    box              integer NOT NULL REFERENCES boxes(id),
    qty_units        integer,
    description      varchar
);
INSERT INTO box_contents VALUES (1, 1, 1, 4, 'cookbook');
INSERT INTO box_contents VALUES (2, 1, 1, 2, 'baby bottle');
INSERT INTO box_contents VALUES (3, 1, 2, 1, 'television');
INSERT INTO box_contents VALUES (4, 2, 3, 2, 'lamp');

Answer 1

您将 JOIN 放入构建索引的 sql_query 中。表保持规范化，但在构建索引时进行非规范化。

它只是一个基本示例，但您的查询将类似于......

sql_query = SELECT o.id,customer_po,UNIX_TIMESTAMP(date_ordered) AS date_ordered,   \
    GROUP_CONCAT(DISTINCT party_type) AS party_type, \
    GROUP_CONCAT(DISTINCT company) AS company, \
    GROUP_CONCAT(DISTINCT city) AS city, \
    GROUP_CONCAT(DISTINCT description) AS description \
  FROM orders o \
    INNER JOIN parties p ON (o.id = p.order_id) \
    INNER JOIN box_contents b ON (o.id = b.order_id) \
  GROUP BY o.id \
  ORDER BY NULL

更新：或者可以使用 sql_joined_field 执行相同的操作，但避免实际的 sql_query 连接。然后 Sphinx 会为您执行加入过程