php - 内部连接性能慢

Question

我有一个包含超过 10 万行的报价表，因此下面的查询非常慢(平均 4 秒)。

SELECT cat1.id AS cat1id, 
    cat1.title_gr AS title, 
    cat1.order

    FROM categories_groups_cat1 AS cat1

    INNER JOIN 
      ( SELECT categories_id, categories_groups_cat1_id FROM
        categories_vs_groups
        GROUP BY categories_groups_cat1_id ) AS vs
    ON vs.categories_groups_cat1_id=cat1.id

    INNER JOIN 
      ( SELECT id, title_gr FROM
        categories
        GROUP BY title_gr ) AS cats
    ON cats.id=vs.categories_id

   INNER JOIN 
      ( SELECT category_gr FROM
        offers
        GROUP BY category_gr ) AS offers
    ON offers.category_gr=cats.title_gr

    GROUP BY cat1.id
    ORDER BY cat1.order ASC 

table 上优惠

`id` int(11) NOT NULL,
`title` text NOT NULL,
`description` text NOT NULL,
`image` text NOT NULL,
`price` float NOT NULL,
`start_price` float NOT NULL,
`brand` text NOT NULL
`category_gr` text NOT NULL

表 categories_groups_cat1

`id` int(11) NOT NULL,
`order` int(11) NOT NULL,
`title_gr` text NOT NULL

表 categories_vs_groups

`id` int(11) NOT NULL,
`categories_groups_cat1_id` int(11) NOT NULL,
`categories_id` int(11) NOT NULL

表格类别

`id` int(11) NOT NULL,
`title_gr` char(255) NOT NULL

我尝试从存在商品的 categories_groups_cat1 中进行选择，这就是我使用内部联接的原因。我不知道它是否完全正确。如果有另一个更快(性能)的解决方案，我将不胜感激

Answer 1

你应该避免创建临时表的子查询。这肯定会提高性能。在内存中创建临时表的子查询会降低性能，尽量避免。

我已经修改了你的代码。可能会有小的语法错误。

  SELECT cat1.id AS cat1id, 
        cat1.title_gr AS title, 
        cat1.order

        FROM categories_groups_cat1 AS cat1

        INNER JOIN 
          categories_groups_cat1_id  AS vs
        ON vs.categories_groups_cat1_id=cat1.id

        INNER JOIN 

            categories
             AS cats
        ON cats.id=vs.categories_id

       INNER JOIN 

            offers

        ON offers.category_gr=cats.title_gr

        GROUP BY cat1.id,cats.title_gr, offers.category_gr
        ORDER BY cat1.order ASC