Select distinct ... inner join vs. select ... where id in (...)
Try this
select * from Users uwhere exists ( select user_id from Log_mview l where l.user_id = u.user_id )/If the sub-query returns a large number of rows WHERE EXISTS can be substantially faster than WHERE ... IN.
This will depend on the data you have, but using Distinct within the join could improve your performance:
Select u.*From Users uJoin ( Select Distinct user_id From log_mview ) l On u.user_id = l.user_id
The second query is probably working more the harddrive than the first query (join+distinc).
The first query will probably translates to something like:
for each row in table Log find corresponding row in table User (in memory). The database is probably smart enough to create in memory structures for table User that is probably much smaller than Log table.I believe that query one (join+distinct) will require only one pass on table Log. The distinct is probably executed in memory.The second query probably forces the database to do multiples fulls reads on table Log.
So in the second query you probably get:
For each row in table user read all the rows in table Log (from disk) in order to match the condition.
You have also to consider that some query may experience a dramatic diference in speed due to changes in memory availability, load and table increase.