I've got a MySQL table for images (with image ID, path etc., not the actual image in a blob). I have a Image class with a function for retrieving an image given an ID as parameter. On some pages there may be anything between 0 and around 30 images.

To the question. How is the page loading time affected if I run one query for each image retrieval, compared to if I were to build a large WHERE clause and retrieve all images at once? Which is the better and how much difference is there?

Thanks
lizciz

What I would do is test both. As well as seeing how each loads in the browser, use EXPLAIN to give you information on each one:

EXPLAIN SELECT ...

I think there will probably be a common criteria for listing images on a single page. Either they all belong to same album, have a common tag, were added on the same date, etc. If that is the case then using where clause to get them all at once will definitely be efficient. But if you're thinking of building a large where clause by adding comparison to image_id lots of times, well then that would be terribly inefficient and probably not required. Re-think why you need all those images on a single page and i am sure, you'd be able to come up with a common criteria for those images to put in where clause.

Ok, I see.
For better understanding I'll take an example. I have a page for news (not news you get on the TV, but news regarding the website), which may contain images. Lets say each set of news can have 6 images, and the 5 most recent news will be shown.

The Image table is used for all (uploaded) images on the site, there is a table for the news (table 'News') and then there is a linking table for these two (table 'NewsImages'). How would I best do to retrieve the 5 news together with their respective images?

I'm thinking I could first retrieve the 5 news, and then for each set of news I'll join Images and NewsImages to get the image paths where the news ID equals to the one from the set of news.
I'm guessing there isn't a way to retrieve the all the news togeather with all of it's images in a single query(?).

Using a join.

http://dev.mysql.com/doc/refman/5.0/en/join.html

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

I'm not sure of what you mean. Is there a way to retrieve all the news and their images in a single query?

Yes. You have to use a join sql statement to join the tables in the query.

Ok, I still don't understand how, since I need both the news and the images and each news can have several images. If there was only 1 image I could get a joined table row like

NewsID, NewsAttr1, NewsAttr2, ImageID

But with several images, lets say 4, it would have to look something like

NewsID, NewsAttr1, NewsAttr2, ImageID, ImageID, ImageID, ImageID

Could you perhaps make up an example query?

http://www.w3schools.com/sql/sql_join.asp

A "joined" recordset would "look" like

NewsID, NewsAttr1, NewsAttr2, ImagePath
NewsID, NewsAttr1, NewsAttr2, ImagePath
NewsID, NewsAttr1, NewsAttr2, ImagePath
NewsID, NewsAttr1, NewsAttr2, ImagePath

Can't write an specific example query without knowing your table structure and the PK / FK relations.

but as a semi non specific example

SELECT n.newsid,n.newsattr1,n.newsattr2,i.imagepath FROM news AS n LEFT JOIN images AS i ON n.newsid = i.newsid WHERE newsid = ID_to_be_selected;

and if you wanted ALL the rows leave off the WHERE clause

using a LEFT JOIN will retrieve all records from the news table even if there are no matching records from the images table

Originally Posted by lizciz

Ok, I still don't understand how, since I need both the news and the images and each news can have several images. If there was only 1 image I could get a joined table row like

NewsID, NewsAttr1, NewsAttr2, ImageID

But with several images, lets say 4, it would have to look something like

NewsID, NewsAttr1, NewsAttr2, ImageID, ImageID, ImageID, ImageID

Could you perhaps make up an example query?

Have you normalized your database?

Originally Posted by VirtuosiMedia

Have you normalized your database?

Uhm, possibly
I'm still not that good with databases and I don't know all the steps in normalization.

My structure looks like this:

table News
NewsID, Date, Content

table Images
ImageID, ImagePath

table NewsImages
NewsID, ImageID

What I meant with my last post was that if I'm going to select 5 news, and lets say they each have 4 images, the rows would have to "look" like this (right?).

1: NewsID1, Date1, Content1, ImageID1.1, ImageID1.2, ImageID1.3, ImageID1.4
2: NewsID2, Date2, Content2, ImageID2.1, ImageID2.2, ImageID2.3, ImageID2.4
3: NewsID3, Date3, Content3, ImageID3.1, ImageID3.2, ImageID3.3, ImageID3.4
4: NewsID4, Date4, Content4, ImageID4.1, ImageID4.2, ImageID4.3, ImageID4.4
5: NewsID5, Date5, Content5, ImageID5.1, ImageID5.2, ImageID5.3, ImageID5.4

Originally Posted by lizciz

Uhm, possibly
I'm still not that good with databases and I don't know all the steps in normalization.

My structure looks like this:

table News
NewsID, Date, Content

table Images
ImageID, ImagePath

table NewsImages
NewsID, ImageID

What I meant with my last post was that if I'm going to select 5 news, and lets say they each have 4 images, the rows would have to "look" like this (right?).

1: NewsID1, Date1, Content1, ImageID1.1, ImageID1.2, ImageID1.3, ImageID1.4
2: NewsID2, Date2, Content2, ImageID2.1, ImageID2.2, ImageID2.3, ImageID2.4
3: NewsID3, Date3, Content3, ImageID3.1, ImageID3.2, ImageID3.3, ImageID3.4
4: NewsID4, Date4, Content4, ImageID4.1, ImageID4.2, ImageID4.3, ImageID4.4
5: NewsID5, Date5, Content5, ImageID5.1, ImageID5.2, ImageID5.3, ImageID5.4

No if you use the left join, the rows will looks like this:

1: NewsID1, Date1, Content1, ImageID1.1
2: NewsID1, Date1, Content1, ImageID1.2
3: NewsID1, Date1, Content1, ImageID1.3
4: NewsID1, Date1, Content1, ImageID1.4
5: NewsID2, Date2, Content2, ImageID2.1
6: NewsID2, Date2, Content2, ImageID2.2
.................................................. ...........

You'll have to filter the duplicate news content in your application code.

Ah, I see. Is it just me or does that seem a bit unnecessary? Wouldn't it be eaiser then to first get all the news, and then run a query for each news to get it's images?

Or maybe I'll try both and see which one is faster

You won't see a lot of difference until you have a LOT of news items.

think about it;

database servers optimise queries "on the fly" to use memory and CPU time efficiently, so a single query pulling all rows required is going to be more efficient than having maybe a thousand or more sequential database trips all using the same query with only the ID changing.

You are thinking small, to design databases and query them successfully and efficiently you have to scale your ideas to HUGE!!!

think in terms of a million+ records to retrieve, then you'll begin to consider what is more efficient.

Point taken.
Thanks for your awnsers everyone!

Originally Posted by lizciz

Ah, I see. Is it just me or does that seem a bit unnecessary? Wouldn't it be eaiser then to first get all the news, and then run a query for each news to get it's images?

Or maybe I'll try both and see which one is faster

The join will be considerably faster. #1 it's what the database is good at. #2 the database was written in C++ and runs compiled code that's heavily performance optimized, while your PHP code is text being interpreted on the server. Anything that runs in the database will be faster than anything you can write in PHP. That isn't a knock on your coding skills, that's the reality of native machine code and interpreters.

imho..

I ever stored image in sql database.. it save your hard drive space but it make your database server works harder, and it takes your user's browser longer time to process.
then I change the code to create a folder based on the id and store the physical image file.

please cmiiw