如何使用Mysql Joins而不是嵌套子查询获得相同的结果? [英] How to achieve the same result using Mysql Joins instead of nested subqueries?

问题描述

我有一些嵌套的子查询，结果却非常慢，而且我正努力用Mysql Joins重写相同的代码，希望有人能为我提供帮助. 我想从数据库中所有歌曲中获取数据，但播放列表中的歌曲除外，此后，我只想要播放列表中的歌曲. 在这里您可以看到我的查询

I've got some nested subquerys which turned out to be VERY slow and I'm struggling to rewrite the same code with Mysql Joins, I hope someone can help me. I want to get the data from all the songs on the database except the ones on my playlist, and after that I only want the ones on my playlist. Here you can see my querys

提前谢谢.

$sql1 = "Select distinct title, artist, album from songs where id not in(Select id from songs where id 
        IN(Select id from songs where title IN(Select title from songs where id 
        IN(Select song_id from playlist where playlist_id IN (Select playlist_id from playlists where name = '$playlist_name')))))";


$sql2 = "Select distinct title, artist, album from songs where id in(Select id from songs where id 
        IN(Select id from songs where title IN(Select title from songs where id 
        IN(Select song_id from playlist where playlist_id IN (Select playlist_id from playlists where name = '$playlist_name')))))";

数据库设计

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


CREATE TABLE IF NOT EXISTS playlist (
  playlist_id int(11) NOT NULL,
track_id int(11) NOT NULL,
  song_id int(11) NOT NULL,
  votes int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1463 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS playlists (
playlist_id int(11) NOT NULL,
  `name` varchar(60) NOT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS songs (
id int(11) NOT NULL,
  path varchar(100) NOT NULL,
  artist varchar(60) NOT NULL,
  title varchar(60) NOT NULL,
  album varchar(50) NOT NULL,
  added_at datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3759 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS users (
userID int(11) NOT NULL,
  voices int(11) NOT NULL,
  pass varchar(18) NOT NULL,
  created_at datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE playlist
 ADD PRIMARY KEY (track_id), ADD KEY song_id (song_id), ADD KEY playlist_id (playlist_id);

ALTER TABLE playlists
 ADD PRIMARY KEY (playlist_id);

ALTER TABLE songs
 ADD PRIMARY KEY (id), ADD UNIQUE KEY title (title,artist,album);

ALTER TABLE users
 ADD PRIMARY KEY (userID);


ALTER TABLE playlist
MODIFY track_id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1463;
ALTER TABLE playlists
MODIFY playlist_id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
ALTER TABLE songs
MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3759;
ALTER TABLE users
MODIFY userID int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE playlist
ADD CONSTRAINT playlist_ibfk_1 FOREIGN KEY (song_id) REFERENCES songs (id) ON DELETE CASCADE,
ADD CONSTRAINT playlist_ibfk_2 FOREIGN KEY (playlist_id) REFERENCES playlists (playlist_id) ON DELETE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

推荐答案

您可以将查询更改为

针对数据库中的所有歌曲(播放列表中的歌曲除外)

For all the songs on the database except the ones on your playlist

$sql1 = "Select distinct title, artist, album 
from songs where id not in(Select distinct song.id
from songs song inner join playlist playlist 
on playlist.song_id=song.id
inner join playlists playlists 
on playlists.playlist_id=playlist.playlist_id
and playlists.name = '$playlist_name')";

播放列表中的那些

$sql2 = "Select distinct song.title, song.artist, song.album
from songs song inner join playlist playlist 
on playlist.song_id=song.id
inner join playlists playlists 
on playlists.playlist_id=playlist.playlist_id
and playlists.name = '$playlist_name'";

这篇关于如何使用Mysql Joins而不是嵌套子查询获得相同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！