mysql relation slow

7
votes
<?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 ;

1
phpmysqlsqldatabase
What columns have indexes? You can run your query with EXPLAIN in the front of it and the output will give you an idea of what's going on. EXPLAIN SELECT m.ID, m.Title, .... – slapyo
basic rule of thumb: any field used in a decision context (e.g. where, join, order, case, etc...` should have an index on it. – Marc B
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 – prog renalin
Please post the SHOW CREATE TABLE for each of these tables. If you have actually defined the foreign key constraints, indexing will be enforced and it will be performant. – Michael Berkowski
show create table inserted – prog renalin

1 Answers

3
votes

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