开发者

sql join with where clause

开发者 https://www.devze.com 2023-02-11 12:08 出处:网络
I am using MYSQL I have 2 tables I would like to join and as the tables get larger I would like to know what would be a better way to query them.The tables are used for a website that are storing pict

I am using MYSQL I have 2 tables I would like to join and as the tables get larger I would like to know what would be a better way to query them. The tables are used for a website that are storing pictures for various users. Kind of like facebook. First table is images table which has an Id and a source location field. and the other table is called user_images with a foreign key with the image id going to the image table. The reason the tables are not put together is because multiple users can have the same picture and doing things this way will do away with redundancy.

Here is what my query would look like to get the source locations of all images for a particular user

SELECT i.source_location 
FROM user_images AS u
INNER JOIN images AS i
ON i.image_id = u.image_id
WHERE u.user_id = 'USER'

So my question is, would it be better to query it for faster results as shown below or does it not matter.

SELECT i.source_location
FROM
(SELECT image_id 
 FROM user_images
 WHERE user_id = 'USER') AS u
INNER JOIN images AS i
ON i.image_id = u.image_id

Basically I would think doing the where clause before the join would make for faster results because the db will have a sh开发者_开发百科orter set to compare to because the join table will be alot smaller. I dont know if this is the right way of going through with this. What would be the best way to go about this? If anyone else has any better ways of doing this please help me out because I see these tables getting rather large.


Does your images table have a user_id field?

If so, just do the INNER JOIN images ON (i.image_id = u.image_id AND i.user_id = "USER")

0

精彩评论

暂无评论...
验证码 换一张
取 消