Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL In can not be overridden with an internal connection     - About the replication of JavaScript (Programming)

- How to enable fbcon in Debian (Linux)

- Analysis RabbitMQ cluster (Server)

- Android LayoutInflater source parsing (Programming)

- Paint with Vim (Linux)

- Java Annotation Comments (Programming)

- MySQL and Oracle time zone settings compare (Database)

- Overall Physical Migration of Oracle Database with (Database)

- Use Aptik Battery Monitor monitoring Ubuntu laptop battery usage (Linux)

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

- Physical backup and recovery SYSTEM table space (Database)

- Monitor traffic Linux Shell Edition (Programming)

- Android and OpenCV2.4.4 build an image processing framework (2013 edition) (Linux)

- For the FreeBSD install Adobe Flash plug-in (Linux)

- Linux find and xargs (Linux)

- Sshuttle A VPN-based transparent proxy that uses ssh (Server)

- The Samba service does not have permission to access (Server)

- Configuring a Linux operating system security management services (Linux)

- Java static code analysis tool Infer (Programming)

- How to contribute code to the GNU Project (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:      
 
- Ubuntu 14.04 install Nmap 6.46.1 (Linux)
- How to add a new hard disk without restarting the CentOS 7 / RHEL 7 virtual machine (Linux)
- Spring REST Exception Handling (Programming)
- Talk about jsonp (Programming)
- Chkconfig command Detailed service is added and shut down the system in two ways to start service under Linux (Linux)
- Linux excellent text editor (Markdown, LaTeX, MathJax) (Linux)
- Samba file sharing server set up (Server)
- Ubuntu 15.04 installed Nvidia Quadro series graphics driver (Linux)
- How to contribute code to the GNU Project (Linux)
- shell script: MySQL monitoring service is normal (Database)
- Android in the coordinate system and a method to obtain the coordinates (Programming)
- A simple shell script for monitoring in Linux (Linux)
- Linux (CentOS) directory file management and file system file compression packing (Linux)
- Android graphic introduction NDK installation and simple jni demo implementation (Linux)
- To install MySQL on Linux (Database)
- Java keyword final, static (Programming)
- Java polymorphic methods inside constructors complete analysis (Programming)
- About Java 7 module system (Programming)
- MySQL 5.7 can not log in problem (Database)
- Ubuntu security settings and tools (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.