CODEWITHSUNDEEP
mysqlsqlquery-optimization
speedy
speedy

Reputation: 375

Optimize mysql multiple left joins

I have a query like this: 

SELECT
  SUM(c.cantitate) AS num,
  p.id             AS pid,
  p.titlu          AS titlu,
  p.alias          AS alias,
  p.gramaj         AS gramaj,
  p.prettotal      AS prettotal,
  p.pretunitar     AS pretunitar,
  p.pretredus      AS pretredus,
  p.stoc           AS stoc,
  p.cant_variabila AS cant_variabila,
  p.nou            AS nou,
  p.congelat       AS congelat,
  p.cod            AS cod,
  p.poza           AS poza,
  cc.seo           AS seo
FROM produse p
  LEFT JOIN (SELECT  produs, cantitate, COS FROM comenzi) c ON p.id = c.produs
  LEFT JOIN (SELECT STATUS, id FROM cosuri) cs ON c.cos = cs.id
  LEFT JOIN (SELECT id, seo FROM categorii) cc ON p.categorie = cc.id
WHERE cs.status = 'closed' AND p.vizibil = '1'
GROUP BY pid
ORDER BY num DESC
LIMIT 0, 14

The query is working, but Duration for 1 query: 2.922 sec. How can I improve the query ?

The keys are as following :

comenzi: cos, produs as unique key

cosuri: id as unique key

produse: titlu, categorie, alias as key

Upvotes: 0

Views: 92

Answers (4)

Ambrish
Ambrish

Reputation: 3677

As you have lot many sub-queries, the time will spent on the full data read. If you apply join directly on the table instead of sub query it will boost the performance.

Try it after removing all subqueries. Like:

SELECT
  SUM(c.cantitate) AS num,
  p.id             AS pid,
  p.titlu          AS titlu,
  p.alias          AS alias,
  p.gramaj         AS gramaj,
  p.prettotal      AS prettotal,
  p.pretunitar     AS pretunitar,
  p.pretredus      AS pretredus,
  p.stoc           AS stoc,
  p.cant_variabila AS cant_variabila,
  p.nou            AS nou,
  p.congelat       AS congelat,
  p.cod            AS cod,
  p.poza           AS poza,
  cc.seo           AS seo
FROM produse p
  LEFT JOIN comenzi c
    ON p.id = c.produs
  LEFT JOIN cosuri cs
    ON c.cos = cs.id
  LEFT JOIN categorii cc
    ON p.categorie = cc.id
WHERE cs.status = 'closed'
    AND p.vizibil = '1'
GROUP BY pid
ORDER BY num DESC
LIMIT 0, 14

You should have following indexes as well:

+---------+------------+
|  TABLE  |   COLUMN   |
+---------+------------+
| produse | categorie  |
| produse | vizibil    |
| comenzi | produs     |
| comenzi | cos        |
| cosuri  | status     |
+---------+------------+

Assuming that id column table is PK in all tables otherwise index is required for these columns as well.

Upvotes: 4

borjab
borjab

Reputation: 11655

Just remove the subqueries. There won't help you. Test this at tell us how much it takes.

SELECT
  SUM(c.cantitate) AS num,
  p.id             AS pid,
  p.titlu          AS titlu,
  p.alias          AS alias,
  p.gramaj         AS gramaj,
  p.prettotal      AS prettotal,
  p.pretunitar     AS pretunitar,
  p.pretredus      AS pretredus,
  p.stoc           AS stoc,
  p.cant_variabila AS cant_variabila,
  p.nou            AS nou,
  p.congelat       AS congelat,
  p.cod            AS cod,
  p.poza           AS poza,
  cc.seo           AS seo
FROM produse p
  LEFT JOIN comenzi   c  ON p.id = c.produs
  LEFT JOIN cosuri    cs ON c.cos = cs.id
  LEFT JOIN categorii cc ON p.categorie = cc.id
WHERE cs.status = 'closed' AND p.vizibil = '1'
GROUP BY pid
ORDER BY num DESC
LIMIT 0, 14

Of course it helps if there is an index for the following fields:

  • p.id (Primary keys are already indexed and won't need to create a new one)
  • cc.id (Primary keys are already indexed and won't need to create a new one)
  • cs.id (Primary keys are already indexed and won't need to create a new one)
  • p.categorie
  • p.vizibil (smaller potential gains here)
  • c.produs
  • c.cos
  • cs.status (smaller potential gains here)
  • p.pid (typical error to forget that group by needs to order records)

Upvotes: 0

Gabber
Gabber

Reputation: 7249

It will be fast

SELECT
  SUM(c.cantitate) AS num,
  p.id             AS pid,
  p.titlu          AS titlu,
  p.alias          AS alias,
  p.gramaj         AS gramaj,
  p.prettotal      AS prettotal,
  p.pretunitar     AS pretunitar,
  p.pretredus      AS pretredus,
  p.stoc           AS stoc,
  p.cant_variabila AS cant_variabila,
  p.nou            AS nou,
  p.congelat       AS congelat,
  p.cod            AS cod,
  p.poza           AS poza,
  cc.seo           AS seo
FROM produse p
  LEFT JOIN comenzi c ON p.id = c.produs
  LEFT JOIN cosuri cs ON c.cos = cs.id
  LEFT JOIN categorii cc ON p.categorie = cc.id
WHERE cs.status = 'closed' AND p.vizibil = '1'
GROUP BY pid
ORDER BY num DESC
LIMIT 0, 14

Upvotes: 0

Charvee Shah
Charvee Shah

Reputation: 720

You can do this:It will take less time

SELECT
  SUM(c.cantitate) AS num,
  p.id             AS pid,
  p.titlu          AS titlu,
  p.alias          AS alias,
  p.gramaj         AS gramaj,
  p.prettotal      AS prettotal,
  p.pretunitar     AS pretunitar,
  p.pretredus      AS pretredus,
  p.stoc           AS stoc,
  p.cant_variabila AS cant_variabila,
  p.nou            AS nou,
  p.congelat       AS congelat,
  p.cod            AS cod,
  p.poza           AS poza,
  cc.seo           AS seo
FROM produse p
  LEFT JOIN comenzi c ON p.id = c.produs
  LEFT JOIN cosuri cs ON c.cos = cs.id
  LEFT JOIN categorii cc ON p.categorie = cc.id
WHERE cs.status = 'closed' AND p.vizibil = '1'
GROUP BY pid
ORDER BY num DESC
LIMIT 0, 14

Upvotes: 0

Related Questions