CODEWITHSUNDEEP
phpmysql
DMande
DMande

Reputation: 341

inner join slow performance

I have a table offers that has over 100k rows so the below query is very slow (4sec - average).

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 offers

`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

table categories_groups_cat1

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

table categories_vs_groups

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

table categories

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

I try to choose from categories_groups_cat1 where offers exist, that's why I use the inner join. I don't know if it is completely correct. If there is another faster(performance) solution I would appreciate it

Upvotes: 0

Views: 327

Answers (1)

seahawk
seahawk

Reputation: 1912

You should avoid sub-query that creates temp table. This will surely improve performance. Sub-queries that create temp table in memory kills performance, try to avoid as much as you can.

I have modified your code. There may small syntactic errors.

  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

Upvotes: 3

Related Questions