MySQL query optimization

Question

Hello,

$res (array)-> (count 50 (!) ) Example:

(
    [1] => Array
        (
            [artistname] => Lady GaGa
            [songname] => Love Games
            [duration] => 3:31
            [url] => 7e91a5ca16ae
            [server] => 3
        )

    [2] => Array
        (
            [artistname] => DJ Layla
            [songname] => Single Lady
            [duration] => 3:20
            [url] => f0906a3087eb
            [server] => 3
        )

    [3] => Array
        (
            [artistname] => Lady Gaga
            [songname] => Bad Romance (Bimbo Jones Clean Radio Remix)
            [duration] => 3:59
            [url] => 36e77d5a80357
            [server] => 3
        )
}

PHP code:

$massquery  = '';
foreach($res as $value)
{
    if(!get_magic_quotes_gpc())
    {
        $value['artistname']    = mysql_escape_string($value['artistname']);
        $value['songname']      = mysql_escape_string($value['songname']);
        $value['duration']      = mysql_escape_string($value['duration']);
        $value['url']           = mysql_escape_string($value['url']);
        $value['server']        = mysql_escape_string($value['server']);
    }

    $value['artistname']    = trim($value['artistname']);
    $value['songname']      = trim($value['songname']);
    $value['duration']      = trim($value['duration']);
    $value['url']           = trim($value['url']);
    $value['server']        = trim($value['server']);

    $sh     = mysql_query("SELECT `artistname`,`songname`,`server` FROM `music` WHERE `artistname`='".$value['artistname']."' AMD `songname`='".$value['songname']."' AND `server`='".$value['server']."' LIMIT 1");
    if(!mysql_num_rows($sh))
    {
        $massquery  .= '("'.$value['artistname'].'", "'.$value['songname'].'", "'.$value['duration'].'", "'.$value['url'].'", "'.$value['server'].'"),';
    }
}

if(!empty($massquery))
{
    $massquery  = substr($massquery, 0, -1);
    $query      = mysql_query('INSERT INTO `music` (`artistname`, `songname`, `duration`, `url`, `server`) VALUES '.$massquery);
}
mysql_close($mysql);

It turns out 50 requests "SELECT" to the database, which is very bad = ( How can I optimize this code?

From answers:

CREATE TABLE `music` (
  `id` int(50) NOT NULL auto_increment,
  `artistname` varchar(50) NOT NULL,
  `songname` varchar(50) NOT NULL,
  `duration` varchar(6) NOT NULL,
  `url` varchar(255) NOT NULL,
  `server` int(5) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `artistname` (`artistname`,`songname`,`server`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `music` VALUES ('test', 'btest', 1);

...

SELECT `artistname` , `songname` , `server`
FROM `music`
WHERE FALSE
OR (
`artistname` = 'test'
AND `songname` = 'btest'
AND `server` = '1'
)
OR (
`artistname` = 'sas'
AND `songname` = 'asf'
AND `server` = '1'
)
LIMIT 0 , 30 

How do I INSERT those songs that are not yet in the database? Sorry for bad english

Answer 1

You want to insert new records only if no other record with the tuple (artistname,songname,server) (already) exists.
If you create a unique index for these three fields MySQL won't insert a doublet. Then you can either use something like

INSERT IGNORE INTO
  tablename
  (a,b,c,x,y,z) 
VALUES
  (1,2,3,4,5,6),
  (7,8,9,10,11,12),
  ...
  (95,96,97,98,99,100)

or a prepared statement, e.g.

$pdo = new PDO("mysql:host=localhost;dbname=test", 'localonly', 'localonly'); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/* test table */
$pdo->exec('
    CREATE TEMPORARY TABLE foo (
        id int auto_increment,
        artistname varchar(64) not null,
        songname varchar(64) not null,
        duration varchar(16) not null,
        url varchar(64) not null,
        server int not null,
        primary key(id),
        unique key (artistname,songname,server)
    )
');

$data = array(
    array(':artistname' => 'Lady GaGa', ':songname' => 'Love Games', ':duration' => '3:31', ':url' => '7e91a5ca16ae', ':server' => 3),
    array(':artistname' => 'DJ Layla', ':songname' => 'Single Lady', ':duration' => '3:20', ':url' => 'f0906a3087eb', ':server' => 3),
    array(':artistname' => 'Lady Gaga', ':songname' => 'Bad Romance (Bimbo Jones Clean Radio Remix)', ':duration' => '3:59', ':url' => '36e77d5a80357', ':server' => 3)
);


/* the "actual" test script */
$stmt = $pdo->prepare('
    INSERT IGNORE INTO
        foo
        (duration, artistname, songname, server, url)
    VALUES
        (:duration, :artistname, :songname, :server, :url)
');
// first run, all three records should be inserted
foreach( $data as $params ) {
    $stmt->execute($params);
}

// second run
// same artist/songname, different server
$newData = $data[0]; $newData[':server'] = 4;
$data[] = $newData;
// and a completly new record
$data[] = array(':artistname' => 'xyz', ':songname' => 'The ABC song', ':duration' => '2:31', ':url' => 'whatever', ':server' => 2);
// again insert all records (including the three that have already been inserted)
foreach( $data as $params ) {
    $stmt->execute($params);
}

/* fetch all records */
foreach( $pdo->query('SELECT * FROM foo', PDO::FETCH_NUM) as $row ) {
    echo join(', ', $row), "\n";
}

prints

1, Lady GaGa, Love Games, 3:31, 7e91a5ca16ae, 3
2, DJ Layla, Single Lady, 3:20, f0906a3087eb, 3
3, Lady Gaga, Bad Romance (Bimbo Jones Clean Radio Remix), 3:59, 36e77d5a80357, 3
4, Lady GaGa, Love Games, 3:31, 7e91a5ca16ae, 4
5, xyz, The ABC song, 2:31, whatever, 2

The first three records have not been duplicated.

Answer 2

Create a single select for all the relevant cases like this, and verify the results by means of PHP:

$sh = "SELECT `artistname`,`songname`,`server` FROM `music` WHERE ";
$pq = ""
foreach($res as $value)
{
if(!get_magic_quotes_gpc())
{
    $value['artistname']    = mysql_escape_string($value['artistname']);
    $value['songname']      = mysql_escape_string($value['songname']);
    $value['duration']      = mysql_escape_string($value['duration']);
    $value['url']           = mysql_escape_string($value['url']);
    $value['server']        = mysql_escape_string($value['server']);
}

$value['artistname']    = trim($value['artistname']);
$value['songname']      = trim($value['songname']);
$value['duration']      = trim($value['duration']);
$value['url']           = trim($value['url']);
$value['server']        = trim($value['server']);

$sh .= $pq . `(artistname`='".$value['artistname']."' AMD `songname`='".$value['songname']."' AND `server`='".$value['server']."')");
$pq = " OR ";
}

$res = mysql_query($sh);

Answer 3

The select query you wrote can't be inside the foreach, or of course it'll query each time. Turn your $res into a long WHERE clause:

$sql = "SELECT `artistname`,`songname`,`server` FROM `music` WHERE FALSE ";
foreach($res as $value)
{
    // ... 

    $sql .= "OR (`artistname`='".$value['artistname']."' AND `songname`='".$value['songname']."' AND `server`='".$value['server'].")";
}

and then run that query against the database and build your INSERT query.