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


5
  • 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, .... Commented Nov 26, 2014 at 19:23
  • basic rule of thumb: any field used in a decision context (e.g. where, join, order, case, etc...` should have an index on it. Commented Nov 26, 2014 at 19:24
  • 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 Commented Nov 26, 2014 at 19:31
  • 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. Commented Nov 26, 2014 at 19:32
  • show create table inserted Commented Nov 26, 2014 at 20:12

1 Answer 1

3

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

Sign up to request clarification or add additional context in comments.

4 Comments

Please explain more :(
If you use the artist_relations.mp3ID, artist_relations.artistID and mp3s.ID attributes in JOIN, GROUP BY or ORDER BY, is desirable to create indexes for these attributes. Be careful, see this discussion.
my english is very badddd, can you get me code to run ? :(
ALTER TABLE artist_relations ADD INDEX (mp3ID); ALTER TABLE artist_relations ADD INDEX (artistID); ALTER TABLE mp3s ADD INDEX (ID);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.