How to optimize if there are too many sql subqueries?-CodePudding

If the table database is like this

device table

```
device_id device_uuid device_status
1001      00000       offline
1002      00000       Online
1003      11111       Offline
1004      11111       Offline
```

task table

```
task_id device_id task_value
50001   1001      Cleaning
50002   1001      Cleaning
50003   1004      Cleaning
```

my sql

select t.* 
from task t 
where t.device_id in (select device_id 
                      from device d1 
                      where d1.device_uuid in (select device_uuid 
                                               from device d 
                                               where d.device_status = 'online'
                                              )
                     )

How to optimize? Thanks!

CodePudding user response：

You can use JOIN or EXISTS :

select t.* 
from task t 
where exists (select 1 
              from device d1 
              where d1.device_id = t.device_id and 
                    d1.device_status = 'online'
             );

CodePudding user response：

Use JOINs. Mimic your thoughts -- "first find the 'online' devices, then ...":

SELECT  t.*
    FROM  device d
    JOIN  device d1 USING(device_uuid)
    JOIN  task t USING(device_id)
    WHERE  d.device_status = 'online';

I'm unclear on the need for touching device twice. Maybe this would suffice:

SELECT  t.*
    FROM  device d
    JOIN  task t USING(device_id)
    WHERE  d.device_status = 'online';

A note:

FROM a JOIN b  USING(x)

is a shorthand for

FROM a JOIN b  ON b.x = a.x

There is another issue -- Indexes. Be sure to have these; if the tables are large, they will help significantly:

device:  INDEX(device_status, device_id)
task:    INDEX(device_id)

Another note: the keywords INNER and OUTER have no functionality in MySQL, hence I left them out.