Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL In can not be overridden with an internal connection     - ogg start being given libnnz11.so: can not open shared object file (Database)

- Linux FAQ - How to fix tar:Exitingwith failure status due to previous errors (Linux)

- Oracle 11g tracking and monitoring system-level triggers to drop misuse (Database)

- Build the first ASP.NET 5 Web project in Mac OS X Yosemite 10.10.3 (Server)

- SecureCRT connect virtual machine Linux system (Ubuntu) (Linux)

- Linux system started to learn: Teaches you install Ubuntu 15.04 on VirtualBox (Linux)

- Large computer network security policy Experience (Linux)

- ORA-00020: No more process state objects available (Database)

- CentOS7 installed MySQL (Database)

- Sniffer install applications in Linux supplement (Linux)

- Java reflection technology explain (Programming)

- Win8 mount disk partitions under Ubuntu (Linux)

- Android components series Activity (Programming)

- Transfer files and permissions from Windows to Linux system by Samba (Linux)

- Ubuntu 14.04 virtual machine switching desktop environments (Linux)

- Install RAID 6 (Striping double distributed parity) (Linux)

- Apache site default home page settings (Server)

- MySQL Data Types (Database)

- Oracle GoldenGate Installation and Configuration Tutorial Introduction (Database)

- Hadoop 2.6.0 stand-alone / pseudo-distributed installation (Server)

 
         
  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:      
 
- Source Analysis: Java object memory allocation (Programming)
- Using Maven to download Spring (Linux)
- What is a logical partition management LVM, how to use in Ubuntu (Linux)
- Three binary tree traversal (recursive, non-recursive traversal and Morris) (Programming)
- Linux dd command make U disk boot disk (Linux)
- Ubuntu rights management common commands (Linux)
- Easy to get hidden administrator account (Linux)
- 10 Regulation of painless SQL Schema (Database)
- Those functions under Linux you do not know the df command (Linux)
- top command causes the system load increases (Linux)
- How to Start a Linux application running in the background using the terminal mode (Linux)
- Iptables small summary (Linux)
- Use in Linux ipmitool tool (Linux)
- Depth study and understanding for individual users suicide DDoS attacks (Linux)
- Differential test piece using MongoDB performance YCSB (Database)
- Linux System Getting Started Learning: DeVeDe installed on Linux to create a video DVD (Linux)
- BusyBox making the file system (Linux)
- Linux terminal program running in the background (Linux)
- PULL operation mechanism parsing XML Comments (Programming)
- Computer security perimeter recommendations (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.