mysql relation slow

<?php
$db = new mysqli(//editted out db credentials);

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}
$sql = "SELECT m.ID, m.Title, GROUP_CONCAT(a.Title) AS Artist
FROM mp3s m
LEFT JOIN artist_relations ar ON ar.mp3ID = m.ID
LEFT JOIN artists a ON a.ID = ar.artistID
GROUP BY m.ID
ORDER BY ID
LIMIT 0,30;
";

if($result = $db->query($sql)){
echo "<table>";
while($row = $result->fetch_assoc()){
    echo "<tr>";  
    echo "<td>".$row['Title']."</td>";
    echo "<td>".$row['Artist']."</td>";
    echo "</tr>";  
}
echo "</table>";
}

?> 

This query working correctly, but the speed is very slow.

This database has 3 fields:

`artists` : ID , Title
`mp3s` : ID , Title
`artist_relations` : mp3ID , artistID

i need this:

row1: titlemusic1 - artist1 , artist4 , artist5
row2: titlemusic2 - artist1
row3: titlemusic1 - artist3 , artist8
row4: titlemusic1 - artist9 , artist10
...

mp3s , artist & artist_relations each have more than 20000 records

EXPLAIN SELECT m.ID, m.Title, .... :
    id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
    1   SIMPLE  m   ALL     NULL    NULL    NULL    NULL    23718   Using temporary; Using filesort
    1   SIMPLE  ar  ALL     NULL    NULL    NULL    NULL    24337   
    1   SIMPLE  a   eq_ref  PRIMARY     PRIMARY     4   ganools_rj.ar.artistID  1   

--

-- Table structure for table artist_relations

CREATE TABLE IF NOT EXISTS artist_relations ( artistID int(11) NOT NULL, mp3ID int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

--

-- Table structure for table artists

CREATE TABLE IF NOT EXISTS artists ( ID int(11) NOT NULL AUTO_INCREMENT, Title varchar(155) NOT NULL PRIMARY KEY ( ID ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9005 ;

---

--

-- Table structure for table mp3s

CREATE TABLE IF NOT EXISTS mp3s ( ID int(11) NOT NULL AUTO_INCREMENT, Title varchar(155) NOT NULL, imageURL varchar(155) NOT NULL, mp3URL varchar(155) NOT NULL, Description text, Lyric text, album varchar(155) DEFAULT NULL, plays int(11) DEFAULT NULL, pubDate date NOT NULL, PRIMARY KEY ( ID ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22936 ;

---

---

Try create index for artist_relations.mp3ID, artist_relations.artistID and mp3s.ID. See CREATE INDEX

链接地址: http://www.djcxy.com/p/59698.html

上一篇: 如何从数据库中获取菜单及其子类

下一篇: mysql关系慢