PDO: check tags presence in db and then insert

Question

Hi, I'm working with PDO connection for mysql and I'd like to have some opinion on a query I use to check if tags are present on the database, and to add it in the case it isn't.

// the tags are allready processed in $tags array 

$check_stmt = $connection->prepare ("SELECT * FROM tags WHERE tag_name = :tag_name");
$save_stmt = $connection->prepare ("INSERT INTO tags (tag_name) VALUES (:tag_name)");

foreach ($tags as $current_tag) {
    $check_stmt->bindParam (':tag_name', $current_tag, PDO::PARAM_STR, 32);
    $save_stmt->bindParam (':tag_name', $current_tag, PDO::PARAM_STR, 32);
    $check_stmt->execute ($current_tag);
    if ($check_stmt->rowCount() == 0) $save_stmt->execute ($current_tag);
}

I'm not skilled with databases so I'm not sure if the query is well projected

Answer 1

I'd adjust your selection query a bit, to optimize:

SELECT 1 AS found FROM tags WHERE tag_name = :tag_name LIMIT 1

SELECTing * transmits much more data (all fields in matching records) from the db to your app than is necessary. selecting only the fields you need is much more efficient, and in this case it looks like you're just checking for existence, so you don't need any record data, therefore the SELECT 1.

The LIMIT 1 limits the query results to one record, instead of all matching ones. Quicker query execution and again less data transfer.

Answer 2

Some dirtier MySQL-specific options include simply using REPLACE INTO (don't) or the IGNORE keyword in combination with INSERT (suggested). The INSERT IGNORE syntax will be slightly faster than executing your SELECT separately.