How a multiple select MySQL query works

Question

I have a question about something basic related to mysql. consider a query like:

select * 
  from logdb_new.new_tbl_logrecord as alltable 
  where alltable.SessionID in (
    select distinct SessionID from logdb_new.new_tbl_health
  )

I wonder if an index on SessionID of new_tbl_health table is used during execution of this query. In other words tell me if the second part of the query, select distinct SessionID from logdb_new.new_tbl_health, first executed generating a list and then the main query executes or it uses an index or whatever on new_tbl_health to find sessionIDs for every row of new_tbl_logrecord.

I want to reduce execution time of my query. logdb_new.new_tbl_logrecord has almost 30 million records and distinct SessionIDs of logdb_new.new_tbl_health are more than 80 thousand results. Please let me know if there is a better query instead to reduce execution time.

Answer 1

I don't think that an index on new_tbl_health.SessionID can be used here, because the result of your subquery is an intermediate result. But, we can try rewriting your query using an inner join:

SELECT *
FROM logdb_new.new_tbl_logrecord t1
INNER JOIN logdb_new.new_tbl_health t2
    ON t1.SessionID = t2.SessionID;

Besides that the index might be usable here, the advantage to an inner join version is that the optimizer is now free to choose which table appears on the left/right sides of the join.

As a side note, if you want to stick with your current approach, I think you can drop the distinct select and just use:

SELECT SessionID FROM logdb_new.new_tbl_health

If duplicate SessionID values arise, it won't logically change the outcome of your query. But invoking DISTINCT would mean the query plan would probably have to do some aggregating, perhaps unnecessarily.