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

--

--

--

just a bug hunter and problem addict

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

⚡️ Making a Static Site Generator with Python — part 1

Regex: /what is it\?/

How to Audit — Azure Key Vault?

How to : launch an AppleScript from AppKit on Catalina with Swift

What Is a Memory FoamMattress https://t.co/ZGyXRIweZj

Facial Recognition with AWS

Get more out of Pull Request Reviews

Ever Hoped That You Shouldn’t Get A SCHOLARSHIP?

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
Abhishek Jakhotiya

Abhishek Jakhotiya

just a bug hunter and problem addict

More from Medium

Time Efficiency of Sorting Algorithms

Merge Sort Explained.

Music at the Museum Artist Feature: Keenan Jenkins of XOXOK

CS 373 Spring 2022: Kyle Kamka