Mysql query : joined or separate

Question

I have two tables:

One is called data and there is only one (unique) row per ID. Second is called images and there are 3 rows per ID.

Every time the page loads i would like to fetch data and one image for exactly 5 different IDs.

My question now is: Two separate SELECT queries or one query where both are joined.

Queries:

...

$all = $row["iD"] // includes **5** last iDs - fetched from DB
$all = implode(',',$all);

SELECT Name, Address FROM data WHERE iD IN($all);
SELECT url FROM images WHERE iD IN ($all) LIMIT 1;

I already have 3 other select queries on page, so i would like to know what is best regarding performance, one bigger - joined or two small - faster queries.

If join, how would these two be joined?

score 1 · Answer 1 · answered Aug 31 '12 at 12:01

1

You have three images per ID and desire one image per ID for the last inserted images (aka "recent content" )?

Then you could use one easy natural join combined with group by like this:

SELECT d.Name, d.Address, MAX(i.url) 
FROM data d, images i
WHERE i.iD = d.iD
GROUP BY d.Name, d.Address
ORDER BY d.iD DESC
LIMIT 5

Most of the time it is better to combine selects to skip the programmitcally overhead (calling mysql_query() in an loop itself for example). But sometimes it depends on the underlying data.

answered Aug 31 '12 at 12:01

Najzero

3,164
18
18

Yes, but image doesnt have to be latest. Just one of the three with suitable ID. :) Thanks – John Aug 31 '12 at 12:04
then just remove the order BY/limit and add an additional AND to the where stating your desired IN ($values) (before the group by) – Najzero Aug 31 '12 at 12:06

score 0 · Answer 2 · answered Aug 31 '12 at 12:04

0

Since your queries go to completely separate tables, I recommend you stay with 2 separate queries: This keeps the result sets smaller and makes it more likely, that at least one stays in the query cache,

Concerning your 2nd query: Do you understand, that this is not guaranteed to fetch a special URL, but any? Mostly the first one by key, but not guaranteed so.

answered Aug 31 '12 at 12:04

Eugen Rieck

64,175
10
70
92

Yes, the url doesnt matter. Any image is fine as long as ID is correct. :) Thanks – John Aug 31 '12 at 12:05

score 0 · Answer 3 · edited May 23 '17 at 10:34

For an answer on performance issues, see JOIN queries vs multiple queries . What I can understand from there is that performance issues vary depending on the specific situation so you should test both.

For the join, you could do;

SELECT User.iD, User.Name, User.Address, Image.url
FROM images as Image
JOIN data as User
  ON Image.iD = User.iD
WHERE Image.iD IN ($all)
LIMIT 1;

It is not tested yet, so you should take it with a grain of salt. It is at least a starting point.

Mysql query : joined or separate

3 Answers3