query performance - MySQL

Question

We have a table named "tags" which has 3 cols: id, value, count which the value is Unique key.

our members are supposed to enter tags for their favorite musics, we're gonna save each member tag in "members.favorites" table, for the auto suggestion we query the "tags" table.

Now a member favorites are: "song1,song2,song3", we put that value exactly like that in "members.favorites", now for putting the in "tags" we're gonna explode that by ',' and insert it into "tags", so "tags" gets 3 rows as "song1", "song2", "song3" with "count=1", now another member comes and enter "song3,song4,song5", for entering this into "tags", "song4" and "song5" will be inserted as a new row since they don't exist, but the "song3" already exists and I need to do "count+1" for "song3".

My code is:

$tags_array =  explode(',', $tags);
foreach($tags_array as $key => $val){
    $check_if_already_exists = mysql_query("SELECT value FROM tags WHERE value = '$val'");
    if(mysql_num_rows($check_if_already_exists)){
        // This already exists, so count++
        mysql_query("UPDATE tags SET count = count+1 WHERE value = '$val'");    
    }else{
        // It's a new tag, insert
        mysql_query("INSERT INTO tags (value, count) VALUES ('$val', '1')");
    }
}

But this seems wired somehow! each user may have 20 favorite song and the above code seems not to have the best performance, anybody got any ideas for improving this?

Thanks

aykut · Accepted Answer · 2012-11-18 09:42:27Z

1

You can use INSERT ... ON DUPLICATE KEY UPDATE syntax

answered Nov 18, 2012 at 9:42

aykut

3,1021 gold badge28 silver badges27 bronze badges

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

Comments

tampe125 · Accepted Answer · 2012-11-18 09:43:53Z

0

You need the instruction called UPSERT.
On MYSQL you can use INSERT... ON DUPLICATE or you can use the REPLACE syntax

answered Nov 18, 2012 at 9:43

tampe125

8,6037 gold badges34 silver badges46 bronze badges

Collectives™ on Stack Overflow

query performance - MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related