<?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>";
}
?>
此查询工作正常,但速度非常慢 .
该数据库有3个字段:
`artists` : ID , Title
`mp3s` : ID , Title
`artist_relations` : mp3ID , artistID
我需要这个:
row1: titlemusic1 - artist1 , artist4 , artist5
row2: titlemusic2 - artist1
row3: titlemusic1 - artist3 , artist8
row4: titlemusic1 - artist9 , artist10
...
mp3s,artist&artist_relations每个都有超过20000条记录
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
- 表artist_relations的表结构
CREATE TABLE IF NOT NOT EXISTS artist_relations
( artistID
int(11)NOT NULL, mp3ID
int(11)NOT NULL)ENGINE = InnoDB DEFAULT CHARSET = latin1;
- 表格艺术家的表格结构
CREATE TABLE IF NOT 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;
- 表mp3的表结构
CREATE TABLE IF NOT 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;
1 回答
尝试为artist_relations.mp3ID,artist_relations.artistID和mp3s.ID创建索引 . 见CREATE INDEX