SQL - Nested query optimization

Question

How can I optimize this query SQL?

CREATE TABLE table1 AS
 SELECT * FROM temp
 WHERE Birth_Place IN
   (SELECT c.DES_COM
      FROM tableCom AS c
      WHERE c.COD_PROV IS NULL)
 ORDER BY Cod, Birth_Date

I think that the problem is the IN clause

Your query is malformed. You have `select *` and `group by`. That doesn't make sense. Provide sample data and desired results. — Gordon Linoff, Sep 29 '19 at 15:01
That points to MySQL 5.7.4 or older. This malformed query can only run there. — The Impaler, Sep 29 '19 at 15:02

score 0 · Answer 1 · answered Sep 29 '19 at 15:00

0

First of all it's not quite valid SQL, since you are selecting and sorting by columns that are not part of the group. What you want to do is called "select top N in group", check out Select first row in each GROUP BY group?

answered Sep 29 '19 at 15:00

Dinu

1,374
8
21

Gordon Linoff · Answer 2 · 2019-09-29T20:41:56.640

0

Your query doesn't make sense, because you have SELECT * with GROUP BY. Ignoring that, I would recommend writing the query as:

SELECT t.*
FROM temp t
WHERE EXISTS (SELECT 1
              FROM tableCom c
              WHERE t.Birth_Place = c.DES_COM AND
                    c.COD_PROV IS NULL
             )
ORDER BY Cod, Birth_Date;

For this, I recommend an index on tableCom(desc_com, cod_prov). Your database might also be able to use an an index on temp(cod, birth_date, birthplace).

edited Sep 29 '19 at 20:41

answered Sep 29 '19 at 15:02

Gordon Linoff

1,242,037
58
646
786

Okay, I remove the GROUP BY clause and I defined indices. I used your query but it took too time to execute this query and it goes outofmemory – jumpy Sep 29 '19 at 15:31
@jumpy . . . Did you add the recommended index, with the two columns? – Gordon Linoff Sep 29 '19 at 20:41

SQL - Nested query optimization

2 Answers2