0
select * from stores 
where schedule_id in    <- this line
(
    select distinct id  from schedules
    where user_id in 
    (
        select distinct Y.id from tb_CMSTempRute X
        join users Y
        on X.kode = Y.account_id 
        where X.flag = 'xxx'
        and X.create_user = 'ADMIN'
    )
    and date in 
    (
        select distinct X.tgl from tb_CMSTempRute X
        where X.flag = 'xxx'
        and X.create_user =  'ADMIN'
    )
)

I have query like above, but it tooks very slow and then I cancel the query. I tried to select only subquery from line 3 until end, the query is going fast, but why when join with query line 1-2 it's going very slow. But if the second line of query where schedule_id in change using join, it's going fast.

and I have other query and have same issue, very slow when using IN.

anyonw know why?

NB

enter image description here

Eggy
  • 522
  • 5
  • 29
  • 1
    When you do `JOIN` and your tables are reasonably indexed then these indexes can be used. When you use `IN` with derived tables (sub-queries) you lose this benefit. – PM 77-1 Aug 04 '22 at 02:28
  • 1
    I found this post, https://stackoverflow.com/questions/1244998/why-is-mysql-join-significantly-faster-than-where-in-subquery – Hi computer Aug 04 '22 at 02:30
  • Does this answer your question? [MySQL "IN" queries terribly slow with subquery but fast with explicit values](https://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values) – kmoser Aug 04 '22 at 05:07
  • Do you need any of those `DISTINCTs`? They seem unnecessary (and part of the slowdown(. – Rick James Aug 04 '22 at 19:14

0 Answers0