Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL In can not be overridden with an internal connection     - Several configuration changes illustrate deployment of PHP (Server)

- Java reflection technology explain (Programming)

- Python type way of comparison (Programming)

- Mac Docker deploy development environment (Server)

- Smooth upgrade to OpenSSH 6.7 Procedure (Linux)

- Oracle Database High-Risk Vulnerability Warning (Database)

- Monitor log file (listener.log) (Database)

- Linux distributed message queue RocketMQ deployment and monitoring - Dual Master (Server)

- PHP with FastCGI and mod_php Comments (Server)

- linux network security experience (Linux)

- 30 Practical Linux system administrators will learn the command (Linux)

- How to use static, class, abstract method in Python (Programming)

- Linux Getting Started tutorial: XWindow what (Linux)

- FreeBSD install Gnome 3 Desktop (Linux)

- Based on Python: OpenCV simple image manipulation (Programming)

- Linux System Getting Started Learning: Disable Ubuntu Apport internal error reporting procedures (Linux)

- OpenWRT environment to build (Linux)

- Oracle 12c In-Memory Study (Database)

- sudoers file parsing (Linux)

- Grading defense against Linux server attacks (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:      
 
- Cross server / client backup command: rsync use (Server)
- How to track performance issues when using the Unity Game Development Android (Programming)
- How to override the plain text files and directories soft connection in linux (Linux)
- Java Foundation - Variables and data types (Programming)
- How to use Aptik to backup and restore Apps/PPAs under ubuntu (Linux)
- Profile Linux users login shell and login to read (Linux)
- Linux beginners to develop the seven habits (Linux)
- Linux Command study manual - GPG command (Linux)
- Java developers question (Programming)
- Comparison of C # and Java (Programming)
- OpenWRT environment to build (Linux)
- Use in Linux ipmitool tool (Linux)
- Modern Objective-C syntax and new features (Programming)
- MongoDB 3.2 to upgrade from 3.0.7 (Database)
- HomeKit Human Interface Guidelines (Linux)
- Red Hat Enterprise Linux configuration VNC multi-user access methods (Linux)
- Installation salt-minion on RHEL5 (Linux)
- Vagrant failed to start, stuck in Waiting for VM to boot solution (Linux)
- Git bulk delete remote tag (Linux)
- GitHub multiplayer co-development configuration (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.