查询在php中非常慢（> 30秒），但在phpmyadmin中运行查询时很快 [英] Query VERY slow (>30s) in php, but fast when running the query in phpmyadmin

查看：701
本文介绍了查询在php中非常慢（> 30秒），但在phpmyadmin中运行查询时很快的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我会认为自己作为初学者在mysql / php，不要震惊，当你看到我的代码：D。我做了一个php函数与mysql查询，这需要很长时间（大部分时间），当我调用该函数。他们很少会被快速执行。在PHPmyadmin中，此php函数中的单个查询运行速度快，没​​有错误。



缓慢函数



< public function getAllMusicDataNew（$ playlist_name，$ mysqli）{

// einzeln alle titel / allekünstler/ alle alben auflisten als array

$ sql1 = 从id不在的歌曲中选择不同的标题，艺术家，专辑（从id
IN的歌曲中选择不同的ID（从歌曲中选择id，其中标题IN（从id
IN从播放列表中，其中playlist_id IN（从名称='$ playlist_name'的播放列表中选择playlist_id）））））;
$ getTitles = $ mysqli-> query（$ sql1）;

$ sql2 =从ID不在的歌曲中选择不同的艺术家（从id IN
中选择不同的歌曲ID（从歌曲中选择歌曲， （从播放列表
中选择song_id，其中playlist_id IN（从播放列表中选择playlist_id，其中name ='$ playlist_name'）））））;
$ getArtists = $ mysqli-> query（$ sql2）;

$ sql3 =选择不同的专辑，艺术家从id不在的歌曲（从歌曲id选择不同的ID（从歌曲选择id从专辑IN（从歌曲选择专辑ID IN来自播放列表
的song_id，其中playlist_id IN（从其中name ='$ playlist_name'的播放列表中选择播放列表id））和相册<
$ getAlbums = $ mysqli-> query（$ sql3）;

$ sql4 =从歌曲中选择不同的标题，艺术家和专辑（id从中选择不同的ID从id
IN中选择歌曲（从歌曲中选择歌曲其中id
IN（从播放列表中选择song_id，其中playlist_id IN（从播放列表中选择playlist_id，其中name ='$ playlist_name'）））））;
$ getTitles_added = $ mysqli-> query（$ sql4）;

$ sql5 =从歌曲中选择不同的艺术家，其中id in（从歌曲中选择不同的ID，其中id IN
（从歌曲中选择歌曲，从播放列表中选择song_id
其中playlist_id IN（从name ='$ playlist_name'的播放列表中选择playlist_id）））））;
$ getArtists_added = $ mysqli-> query（$ sql5）;

$ sql6 =选择不同的专辑，艺术家从歌曲id（从ID IN中选择歌曲的不同的ID（从歌曲选择id从专辑IN（从歌曲id选择专辑从播放列表
其中playlist_id IN（从名称='$ playlist_name'的播放列表中选择播放列表_id）和专辑<>）））;
$ getAlbums_added = $ mysqli-> query（$ sql6）;


while（$ row = $ getTitles-> fetch_assoc（））{

$ results_array1 [] = $ row;


}

while（$ row = $ getArtists-> fetch_assoc（））{

$ results_array2 [] = $行;


}

while（$ row = $ getAlbums-> fetch_assoc（））{

$ results_array3 [] = $行;


}


if（mysqli_num_rows（$ getTitles_added）！= 0）{


while （$ row = $ getTitles_added-> fetch_assoc（））{

$ results_array4 [] = $ row;


}


while（$ row = $ getArtists_added-> fetch_assoc（））{

$ results_array5 [] = $ row;


}
while（$ row = $ getAlbums_added-> fetch_assoc（））{

$ results_array6 [] = $ row;


}

} else {


$ results_array4 [] =empty;

$ results_array5 [] =empty;

$ results_array6 [] =empty;


}
// print_r（$ titles）;
$ titles [titles] = $ results_array1;
$ artists [artists] = $ results_array2;
$ albums [albums] = $ results_array3;
$ titles_added [titles_added] = $ results_array4;
$ artists_added [artists_added] = $ results_array5;
$ albums_added [albums_added] = $ results_array6;


// printf（$ array_merge（$ results_array1，$ results_array2，$ results_array3））;
return array_merge（$ titles，$ artists，$ albums，$ titles_added，$ artists_added，$ albums_added）;


}



数据库



   -  phpMyAdmin SQL转储
  - 版本4.2.12deb2 + deb8u1 
  -  http://www.phpmyadmin.net 
  -  
  -  Host：localhost 
  -  Erstellungszeit：10. Aug 2016 um 17:04 
  - 服务器版本：5.5.44-0 + deb8u1 
 -  PHP-Version：5.6.23-0 + deb8u1 
 
 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 * /; 
 
  -  
  -  Datenbank：`musicbox2` 
  -  
 
  -  ------------- ------------------------------------------- 
 
  -  
  -  TabellenstrukturfürTabelle`playlist` 
  -  
 
如果NOT EXISTS`playlist`（
`playlist_id` int NULL，
`track_id` int（11）NOT NULL，
`song_id` int（11）NOT NULL，
` InnoDB AUTO_INCREMENT = 1189 DEFAULT CHARSET = latin1; 
 
  -  ---------------------------------------- ---------------- 
 
  -  
  -  TabellenstrukturfürTabelle`playlists`
  -  
 
 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 = 13 DEFAULT CHARSET = latin1; 
 
  -  ---------------------------------------- ---------------- 
 
  -  
  -  TabellenstrukturfürTabelle`songs` 
  -  
 
 CREATE TABLE IF NOT EXISTS``````````````````````````` NOT NULL，
`title` varchar（60）NOT NULL，
`album` varchar（50）NOT NULL，
`added_at` datetime NOT NULL 
）ENGINE = InnoDB AUTO_INCREMENT = 4803 DEFAULT CHARSET = latin1; 
 
  -  ---------------------------------------- ---------------- 
 
  -  
  -  TabellenstrukturfürTabelle`users'
  -  
 
 CREATE TABLE IF NOT EXISTS`users`（
`userID` int（11）NOT NULL，
`voices` int（11）NOT NULL，
`pass` varchar NOT NULL，
`created_at` datetime NOT NULL 
）ENGINE = InnoDB DEFAULT CHARSET = latin1; 
 
  -  
  - 显示表格
  -  
 
  -  
  - 显示表格b $ b  -  
 ALTER TABLE`playlist` 
 ADD PRIMARY KEY（`track_id`），ADD KEY`song_id`（`song_id`），ADD KEY`playlist_id` 
 
  -  
  - 显示表单playlists
  -  
 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`） ; 
 
  -  
  -  AUTO_INCREMENTfürexportierte Tabellen 
  -  
 
  -  
  -  AUTO_INCREMENTfürTabelle`playlist` 
  -  
 ALTER TABLE`playlist` 
 MODIFY`track_id` int（11）NOT NULL AUTO_INCREMENT，AUTO_INCREMENT = 1189; 
  -  
  -  AUTO_INCREMENTfürTabelle`playlists` 
  -  
 ALTER TABLE`playlists`
 MODIFY`playlist_id` int（11）NOT NULL AUTO_INCREMENT，AUTO_INCREMENT = 13; 
  -  
  -  AUTO_INCREMENTfürTabelle`songs` 
  -  
 ALTER TABLE`songs` 
 MODIFY`id` int（11）NOT NULL AUTO_INCREMENT，AUTO_INCREMENT = 4803; 
  -  
  -  AUTO_INCREMENTfürTabelle`users'
  -  
 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`）参考`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 * /;

Sql进程列表

解决方案

它的工作:)仍然dunno为什么嵌套查询花费了40-50秒。
感谢用户Dibakar Paul帮助我！

  $ sql1 =选择不同的标题，艺术家，专辑
从id不在的歌曲（选择不同的song.id 
从歌曲歌曲内部加入播放列表播放列表
在playlist.song_id = song.id 
内部加入播放列表播放列表
 playlists.playlist_id = playlist.playlist_id 
 and playlists.name ='$ playlist_name'）; 
 
 $ sql2 =选择不同的song.title，song.artist，song.album 
从歌曲歌曲内部加入播放列表播放列表
 on playlist.song_id = song.id 
 inner join playlists playlists 
 on playlists.playlist_id = playlist.playlist_id 
 and playlists.name ='$ playlist_name';


I would consider myself as beginner in mysql/php, dont be to shocked when you see my code :D . I made a php function with mysql queries which take very long (most of the time) when I call the function. They rarely will be executed fast. In PHPmyadmin the single queries in this php function run fast and without errors.

Slow Function

       public function getAllMusicDataNew($playlist_name, $mysqli) {

// einzeln alle titel / alle künstler / alle alben auflisten als array 

    $sql1 = "Select distinct title, artist, album from songs where id not in(Select distinct 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')))))";
    $getTitles = $mysqli->query($sql1);

    $sql2 = "Select distinct artist from songs where id not in(Select distinct id from songs where id IN
    (Select id from songs where artist IN(Select artist from songs where id IN(Select song_id from playlist
    where playlist_id IN (Select playlist_id from playlists where name = '$playlist_name')))))";
    $getArtists = $mysqli->query($sql2);

    $sql3 = "Select distinct album, artist from songs where id not in( Select distinct id from songs where id IN(Select id from songs where album IN( Select album from songs where id IN( Select song_id from playlist
    where playlist_id IN ( Select playlist_id from playlists where name = '$playlist_name'))and album <> '')))";
    $getAlbums = $mysqli->query($sql3);

    $sql4 = "Select distinct title, artist, album from songs where id in(Select distinct 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')))))";
    $getTitles_added = $mysqli->query($sql4);

    $sql5 = "Select distinct artist from songs where id  in(Select distinct id from songs where id IN
    (Select id from songs where artist IN(Select artist from songs where id IN(Select song_id from playlist
    where playlist_id IN (Select playlist_id from playlists where name = '$playlist_name')))))";
    $getArtists_added = $mysqli->query($sql5);

    $sql6 = "Select distinct album, artist from songs where id in( Select distinct id from songs where id IN(Select id from songs where album IN( Select album from songs where id IN( Select song_id from playlist
    where playlist_id IN ( Select playlist_id from playlists where name = '$playlist_name'))and album <> '')))";
    $getAlbums_added = $mysqli->query($sql6);


while ($row = $getTitles->fetch_assoc()) {

      $results_array1[] = $row;


    }

        while ($row = $getArtists->fetch_assoc()) {

      $results_array2[] = $row;


    }

        while ($row = $getAlbums->fetch_assoc()) {

      $results_array3[] = $row;


    }


    if(mysqli_num_rows($getTitles_added)!=0) {


        while ($row = $getTitles_added->fetch_assoc()) {

      $results_array4[] = $row;


    }


        while ($row = $getArtists_added->fetch_assoc()) {

      $results_array5[] = $row;


    }
    while ($row = $getAlbums_added->fetch_assoc()) {

      $results_array6[] = $row;


    }

    } else {


      $results_array4[] = "empty";

      $results_array5[] = "empty";

      $results_array6[] = "empty";


     }
//  print_r($titles);
        $titles["titles"] = $results_array1;
    $artists["artists"] = $results_array2;
    $albums["albums"] = $results_array3;
    $titles_added["titles_added"] = $results_array4;
    $artists_added["artists_added"] = $results_array5;
    $albums_added["albums_added"] = $results_array6;


        //  printf($array_merge($results_array1, $results_array2,      $results_array3));
            return array_merge($titles, $artists, $albums, $titles_added, $artists_added, $albums_added);


}

Database

-- phpMyAdmin SQL Dump
-- version 4.2.12deb2+deb8u1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 10. Aug 2016 um 17:04
-- Server Version: 5.5.44-0+deb8u1
-- PHP-Version: 5.6.23-0+deb8u1

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 */;

--
-- Datenbank: `musicbox2`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `playlist`
--

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=1189 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `playlists`
--

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=13 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `songs`
--

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=4803 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `users`
--

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;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `playlist`
--
ALTER TABLE `playlist`
 ADD PRIMARY KEY (`track_id`), ADD KEY `song_id` (`song_id`), ADD KEY `playlist_id` (`playlist_id`);

--
-- Indizes für die Tabelle `playlists`
--
ALTER TABLE `playlists`
 ADD PRIMARY KEY (`playlist_id`);

--
-- Indizes für die Tabelle `songs`
--
ALTER TABLE `songs`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `title` (`title`,`artist`,`album`);

--
-- Indizes für die Tabelle `users`
--
ALTER TABLE `users`
 ADD PRIMARY KEY (`userID`);

--
-- AUTO_INCREMENT für exportierte Tabellen
--

--
-- AUTO_INCREMENT für Tabelle `playlist`
--
ALTER TABLE `playlist`
MODIFY `track_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1189;
--
-- AUTO_INCREMENT für Tabelle `playlists`
--
ALTER TABLE `playlists`
MODIFY `playlist_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT für Tabelle `songs`
--
ALTER TABLE `songs`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4803;
--
-- AUTO_INCREMENT für Tabelle `users`
--
ALTER TABLE `users`
MODIFY `userID` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `playlist`
--
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 */;

Sql Process list

解决方案

With a Join query like this I have the same results and its working :) still dunno why the nested queries took up to 40-50seconds. Thanks to user Dibakar Paul who helped me!

$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'";

这篇关于查询在php中非常慢（> 30秒），但在phpmyadmin中运行查询时很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆