MYSQL : JOIN vs IN clause performance

CREATE TABLE test SELECT * FROM actions_live_table where id IN (SELECT id from <reference_table created from union>);
*************************** 1. row ***************************id: 1select_type: PRIMARYtable: actions_live_tabletype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 17977082Extra: Using where*************************** 2. row ***************************id: 2select_type: DEPENDENT SUBQUERYtable: <table created from union>type: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 38328Extra: Using where
CREATE TABLE test SELECT * FROM actions_live_table LEFT JOIN 
(SELECT id from <table created from union>) as ref_table on ref_table.id=actions_live_table.id where ref_table.id is not null
*************************** 1. row ***************************id: 1select_type: PRIMARYtable: visits_tabletype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 38328Extra: Using where*************************** 2. row ***************************id: 1select_type: PRIMARYtable: actions_live_tabletype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: action_table.idrows: 1Extra: NULL

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store