[SOLVED] Question on query efficiency

[baconbeastnz]

Hi there, I am hoping to be in a high load environment ( ) . Which would be more efficient.

 

For each photo I want to show associated comments.

 

So I could in table photo album have a comma seperated list of the comment ID's which relate to the comment table. This simply requires finding each row id.

 

OR I could simply search the comment table where pictureID = 7. This requires searching on the comment table which will grow huge.

 

So i guess my question is, when you specify a row id of the unique identifier does SQL actually search or does it just grab, as opposed to finding one with pictureID 7 (which is not a unique identifier) it would have to search...?

[fenway]

Don't mess up db normalization with the hopes of a high-load environment.  solve your current problem now.

[corbin]

"So i guess my question is, when you specify a row id of the unique identifier does SQL actually search or does it just grab, as opposed to finding one with pictureID 7 (which is not a unique identifier) it would have to search...?"

 

 

 

A unique constraint is basically just an index that requires only 1 of each entry to be present.

 

 

As such, in your terms, yes MySQL would just "grab" a unique row, but, with indexes it could also just "grab" non-unique rows.

 

 

So yes, as fenway said, stick with a normalized database.  The only time you should ever stray from normalizing your database is if it provides a needed performance boost, and there's no other way to achieve that.  (For example, in this situation you should just use indexes.)

 

 

[baconbeastnz]

Hey guys, thanks for the reply. It wasn't the best phrased question haha, was a bit tired.

 

Will proceed with a normalised DB. Thx for the input