针对1对*关系的MySQL优化

Question

Ok, I've been trying to figure this out but haven't been able to come to a conclusion. I'd love to get some results from y'all, tips to do some more testing, or some more resources to read.

The situation is simple. There is a primary table of pictures. Probably of cat_pictures. There is a secondary table of cat_pictures_comments. Some smart developer made sure that cat_pictures has an auto-incrementing primary key of ID_PICTURE, and comments are stored with an index of ID_PICTURE as well.

Our application has a page that wants to show all of the pictures with all of each pictures comment.

Do we

1. Just INNER JOIN cat_pictures with cat_pictures_comments(and make sure things are ordered correctly)

2. Get all cat_pictures, then loop through each and get cat_pictures_comments for each picture

A: What if the application is PHP?

B: What if cat_pictures had the entry ID, picture filepath, and another 20,285 fields for the cat's genome? And the cat_pictures were also INNER JOIN'ed with each cat's owner's psychiatrist (a 1-1 relationship). ( Basically, there's way more data attached to the primary table compared to our little pissant comments table. )

Thanks all.

Answer 1

I think your question is, do you join back to the comments and then have a query that looks like

Option 1

select (all pic columns), (all comment columns) from pics inner join comments; 

Vs. Option 2

Select * from pics; then loop and select * from comments were pictureId = @currentId;

Vs. Option 3

Select * from pics order by picId; select * from comments order by picId;

In option 1, you have every column of the picture table duplicated for every comment that has been posted for that picture. If there are many comments for each picture and the picture table is very wide, then you will likely be better off to pull two separate datasets from the database.

In option 2, you would be making too many round trips to the database if there were many pictures.

Based on the lack of complete requirements, I would recommend option 3. Get the picture table results and comments as two separate datasets, and then in your php page: loop through each pic and display pic info, then nested loop to get the comments for the current picture from the local dataset of comments that has already been pulled from SQL Server.