I have some explaining to do!

Dolemite50

Hello,

I am trying to go back through all of my tables and optimize them. I have a general question here.

I have a site that allows members to add items to their favorites. The table only stores two items; user_id and item_id. When they attempt to add an item to their favorites I do a check to make sure that there is not an entry for that user_id and item_id already existing and deny it if there is.

I used this: EXPLAIN SELECT * FROM table WHERE user_id = 1

and it shows null for all fields. In a situation like this is it better to add another auto-incrementing field called fave_id and set it to index? I know it would take more space but would it speed things up?

I'd love to hear any general advice that you could give me on this matter.
Thanks a lot!

leatherback

Why not just:

"select * from TABLE where user_id = '$uderid' and item_id = '$item_id'"

And use mysql_num_row($result); to se whether there is a match?

Dolemite50

Thanks,

I'm sorry, that is actually what I am doing. I am just reading up on some stuff and they seem to be against having an explain statement "not defined" and having "nulls". THe method you mentioned has been working for me for me but I am wondering if my proposed method would benefit me at all.

I'm sorry, I'm just a noob trying to "do it right".

mrhappiness

Dolemite50 wrote:
Hello,

I am trying to go back through all of my tables and optimize them. I have a general question here.

I have a site that allows members to add items to their favorites. The table only stores two items; user_id and item_id. When they attempt to add an item to their favorites I do a check to make sure that there is not an entry for that user_id and item_id already existing and deny it if there is.

ALTER TABLE favorites
ADD UNIQUE KEY user_item(user_id, item_id)

Now the mysql server itself doesn't allow duplicates (you can do the same thing in phpmyadmin btw.)

if you do an INSERT it works just fine if the entry is not present already, if it is already present you will return an mysql error message.
if you use REPLACE you will not get any error at all