Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL In can not be overridden with an internal connection     - Apache Tomcat integration and resin (Server)

- Customize own small private Linux system (Linux)

- Java Timer (regular calling, to achieve a fixed time to perform) (Programming)

- Oracle query start with connect by tree (Database)

- How do you know your public IP terminal in Linux (Linux)

- Android Dynamic efficiency articles: a brilliant Loading Analysis and Implementation (Programming)

- Spark SQL job of a lifetime (Server)

- Qt shared memory interprocess communication (Programming)

- Java List add duplicate the same object (Programming)

- Linux initialization init systems - Systemd (Linux)

- Jetty JNDI Development combat (Linux)

- Android Fragment really fully resolve (Programming)

- How to manage and use Logical Volume Management LVM in Ubuntu (Linux)

- Understand the security restore accidentally deleted critical system files (Linux)

- CentOS 6 kernel upgrade to Kernel 3.x (Linux)

- Linux System Getting Started Tutorial: mounted directly in Linux LVM partition (Linux)

- Install VMware Tools in Debian (Linux)

- How nodeclub constructed Docker image (Server)

- Nine artifact control disk partition under Linux (Linux)

- Oracle 11gr2 new APPEND_VALUES tips (Database)

 
         
  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:      
 
- Android Activity launchMode (Programming)
- Slow update statement Performance Analysis (Database)
- How to install OpenOffice Ubuntu or Linux Mint (Linux)
- Linux System Getting Started tutorial: Ubuntu desktop using the command line to change the system proxy settings (Linux)
- MongoDB Learning the notes (Database)
- Install NetBeans IDE 8.0 on Ubuntu, Linux Mint, Elementary OS, and Debian (Linux)
- Java code JIT compiler-friendly Mody (Programming)
- To achieve Linux Security (Linux)
- Linux maximum number of threads and limit the number of queries the current thread (Linux)
- Digital jQuery scrolling effect (Programming)
- Forgot Linux root password (Linux)
- Shell script to crawl through AWR SQL Report Problems (Database)
- An Analysis of the C Algorithm for Calculating the Number of Days Between Date (Programming)
- How to enable curl command HTTP2 support (Linux)
- To install Emacs under CentOS 6.5 (Linux)
- Three easy open source firewall on the Linux platform (Linux)
- The difference between Linux su and sudo commands (Linux)
- Java concurrent programming combat (using synchronized synchronization method) (Programming)
- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)
- Repair after installing Ubuntu no boot device error (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.