Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL In can not be overridden with an internal connection     - Talking about modern programming language syntax and standard library tightly bound phenomenon (Programming)

- PostgreSQL Select source code analysis (Database)

- Linux file permissions bit forced bits and adventure Comments (Linux)

- pureftpd basis: Install, configure, implement, anonymous logon (Linux)

- HBase vs Oracle (Database)

- Bootstrap 3.3.5 release download, Web front-end UI framework (Linux)

- MySQL rollback insert statement causes ibd file grows double (Database)

- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)

- Ubuntu U disk do not have write privileges can only read but not write (Linux)

- Tomcat session clustering and server session (Server)

- Using Vagrant create cross-platform development environment (Linux)

- Linux Timing task Crontab command Detailed (Linux)

- The most concise explanation of JavaScript closures (Programming)

- Install Gnome Flashback Classic Desktop on Ubuntu 14.10 / Mint 7 (Linux)

- Samba file sharing server set up (Server)

- Linux OOM killer mechanism (Linux)

- How to modify the Linux NIC eth1 to eth0 (Linux)

- Linux novice common commands (Linux)

- How to use the DM-Crypt encryption Linux File System (Linux)

- Linux and Windows virtual machine shared folders ---- Linux install VMware tools (Linux)

 
         
  MySQL In can not be overridden with an internal connection
     
  Add Date : 2017-08-31      
         
         
         
  The original interview time, as the interview questions back.
What MySQL In Exists way, very slow ...
Interviewer asked how to do .. answer with internal connection rewriting ..
Then the interviewer revealed a satisfactory smile.

It's always been the script.
It was not until yesterday that I found out that the script was wrong.

Experimental data
Create table song (
 Songid int
 );
 Insert into song values (1), (2), (3);

 Create table song_category_relation (
 Category varchar (10),
 Songid int
 );
 Insert into song_category_relation values
 ( 'Mandarin', 1),
 ( 'Mandarin', 2),
 ( 'Mandarin', 3),
 ( 'Hot', 3);

 Song is the song table
Song_category_relation is the mapping relationship between songs and categories.
A song may have more than one category.

demand.
Find the song ID of a song, find all the categories it belongs to, and then find all the songs under all categories.

Use In
 Select * from song where songid in (
 Select songid from song_category_relation where Category in
 (Select Category from song_category_relation where songid = 3)
 );

But at this time if the connection with the rewrite.
Select * from song inner join (
 Select songid from song_category_relation where Category in
 (Select Category from song_category_relation where songid = 3)
 ) A on (a.songid = song.songid);

As you can see, the results of the two approaches are not the same.
Logically, the subquery after In is de-emphasized.
The original is achieved through the Exists .MySQL 5.6, the use of Semi Join optimization.
     
         
         
         
  More:      
 
- Binary tree traversal: the first sequence in order preorder recursive and non-recursive and traversal sequence (Programming)
- Oracle data row split multiple lines (Database)
- Ubuntu treated with cue file to ape and wav files automatically track points (Linux)
- Four levels of intrusion on Linux server and counter-measures (Linux)
- Android engineers interview questions (Programming)
- Install Firefox 32 official version of the Linux system (Linux)
- Linux --- file descriptors and redirection (Linux)
- Linux host system monitoring implement the cgi (Server)
- Oracle 11g through SCN do incremental backup repair standby library detailed process (Database)
- Oracle database, some basic grammatical structures (Database)
- [SHELL] MySQL primary recovery solution from + Keepalived online (Server)
- Oracle 11g principle study Dataguard (Database)
- About phpwind 5.01-5.3 0day analysis of the article (Linux)
- JavaScript is implemented without new keywords constructor (Programming)
- Linux Oracle environment variable is invalid Problem Solving (Database)
- Install and use automated tools Ansible in CentOS 7 (Linux)
- Linux add a new hard disk (Linux)
- Understanding Java classes (Programming)
- Java List add duplicate the same object (Programming)
- LAMP environment to build Apache, MySQL, PHP under Ubuntu (Server)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.