Multiple MYSQL queries against multiple php foreach loops

Question

Database structure:

id galleryId type file_name description 1 `artists_2010-01-15_7c1ec` `image` `band602.jpg` `Red Umbrella Promo` 2 `artists_2010-01-15_7c1ec` `image` `nov7.jpg` `CD Release Party` 3 `artists_2010-01-15_7c1ec` `video` `band.flv` `Presskit` 

I am going to pull out images for one section of the application, video on another, etc. Is it better to make multiple mysql queries for each section like this:

 $query = mysql_query("SELECT * FROM galleries WHERE galleryId='$galleryId' && type='image'); 

... Or do I need to build an associative array and just iterate over the array through the array when I need to use a result set?

Thanks for the thoughts.

Answer 1

It depends on what is more important: readability or performance. I expect that a single request and prefilling of PHP arrays will be faster to execute, since database connections are expensive, but then a simple request for each section is much more readable.

If you don’t know (and not only hope), you will get a huge amount of traffic, which I’m going to split into separate requests, and then worry about optimization if it looks like this will be a problem. At this point, you will need other things, such as creating a data access layer and adding some caching.

Answer 2

If in the "sections" section you mean separate separate pages (separate HTTP requests) that users can browse, I would suggest a type request as necessary. If on a page where there are only image data sets, you really don't need to take a video data set, for example. You won’t be able to save a lot of time fetching everything, since you still connect to the database for each page screen (suppose.)

If in the "sections" section you mean different parts of one page, then take it all at once. This will save you time on a request (only one request.)

But, depending on the size of your dataset, you may run into problems with the request to limit the amount of PHP memory for everything. Then you can try to increase the memory limit, but if that fails, you will probably have to return to the query for the type.

Using the query-per-type approach transfers part of the computational load to the database server, since you will request and receive what you really need. And you do not need to write code to filter and sort the results. Filtering and sorting is that the database is generally better than the PHP code. If at all possible, turn on the MySQL query cache, which will speed up these queries much more than anything you could write in PHP.

Answer 3

If your data comes from one table, I would make only one query.

I assume that you are creating a single page with a section for images, a section for video, a section for music, etc. Write query results sorted by media iteration type across all images, then all videos, then all music.

Answer 4

Better to have a few queries. Each time you run a query, all data is retrieved and loaded into memory. If you have 5 different types, this means that each page of this type loads 5 times more data than necessary.

Even with one at a time, you probably want to start pagination with LIMIT / OFFSET requests pretty quickly if you have more than 100 or how much you could reasonably display on one page at a time.