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

🚀 Building a Telegram Bot with AWS API Gateway and AWS Lambda

Game Dev Digest Issue #71 — Tech Talks, Adventure Games, AI, Shaders

Show a route between two coordinates on GoogleMaps with Swift

Fibonacci Revisited(Revisited(Revisited(…)))

My First Django Girls

Python multi-thread ,multiprocessing, GIL

Interactive Slack bot using AWS Serverless

Mongos in Containers: Pitfalls, Tips and Tricks

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

CORE Chronicles: Why I left the CTU Caucus of Rank & File Educators

🎇Pay for Your Education in DSCPL Tokens!🎇

Our Brains Consume 30X More Information Than We Can Handle. How Can We Reduce Brain Overload?

All You Need to Know About Git!