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'm going to pull images out for one section of an application, videos on another, etc. Is it better to make multiple mysql queries for each section like so:
我要为应用程序的一个部分提取图像,在另一个部分提取视频等。为每个部分制作多个mysql查询是否更好:
$query = mysql_query("SELECT * FROM galleries WHERE galleryId='$galleryId' && type='image');
...Or should I be building an associative array and just looping through the array over and over whenever I need to use the result set?
...或者我应该构建一个关联数组,只要我需要使用结果集就一遍又一遍地遍历数组?
Thanks for the thoughts.
谢谢你的想法。
4 个解决方案
#1
9
It depends what's more important: readability or performance. I'd expect a single query and prefilling PHP arrays would be faster to execute, since database connections are expensive, but then a simple query for each section is much more readable.
这取决于什么更重要:可读性或性能。我希望单个查询和预填充PHP数组执行起来会更快,因为数据库连接很昂贵,但是每个部分的简单查询都更具可读性。
Unless you know (and not just hope) you're going to get a huge amount of traffic I'd go for separate queries and then worry about optimising if it looks like it'll be a problem. At that point there'll be other things you'll want to do anyway, such as building a data access layer and adding some caching.
除非你知道(而不仅仅是希望)你将获得大量的流量,我会去单独查询,然后担心优化,如果它看起来像是一个问题。此时,您还需要做其他事情,例如构建数据访问层和添加一些缓存。
#2
2
If by "sections" you mean separate single pages (separate HTTP requests) that users can view, I would suggest query-per-type as needed. If on a page where there are only image data sets, you really don't need to fetch the video data set for example. You won't be really saving much time fetching everything, since you will be connecting to the database for every page hit anyway (I assume.)
如果“部分”是指用户可以查看的单独的单个页面(单独的HTTP请求),我建议根据需要使用每个类型的查询。如果在只有图像数据集的页面上,您实际上不需要获取视频数据集。你不会真正节省很多时间来获取所有东西,因为无论如何你将连接到数据库中的每一页(我假设。)
If by "sections" you mean different parts of one page, then fetch everything at once. This will save you time on querying (only one query.)
如果“部分”表示一页的不同部分,则立即获取所有部分。这将节省您查询的时间(只有一个查询。)
But depending on the size of your data set, you could run into trouble with PHP's memory limit querying for everything, though. You could then try raising the memory limit, but if that fails you'll probably have to fall back to query-per-type.
但是,根据数据集的大小,您可能会遇到PHP内存限制查询所有内容的问题。然后,您可以尝试提高内存限制,但如果失败,您可能不得不回退到每个类型的查询。
Using the query-per-type approach moves some of the computing load to the database server, as you will only be requesting and fetching what you really need. And you don't have to write code to filter and sort your results. Filtering and sorting is something the database is generally better at than PHP code. If at all possible, enable MySQL's query cache, that will speed up these queries much more than anything you could write in PHP.
使用每个类型的查询方法将一些计算负载移动到数据库服务器,因为您只需要请求并获取您真正需要的内容。而且您不必编写代码来过滤和排序结果。过滤和排序是数据库通常比PHP代码更好的东西。如果可能的话,启用MySQL的查询缓存,这将比在PHP中编写的任何内容更加加速这些查询。
#3
1
If your data is all coming from one table, I would only do one query.
如果您的数据全部来自一个表,我只会进行一次查询。
I presume you are building a single page with a section for pictures, a section for video, a section for music, etc. Write your query return results sorted by media type - iterate through all the pictures, then all the video, then all the music.
我假设您正在构建一个单页,其中包含一个图片部分,一个视频部分,一个音乐部分等。编写您的查询返回结果按媒体类型排序 - 遍历所有图片,然后是所有视频,然后全部音乐。
#4
0
Better to have multiple queries. Every time you run a query all the data is getting pulled out and loaded into memory. If you have 5 different types, it means each page of that type is loading 5 times as much data as it needs to do.
最好有多个查询。每次运行查询时,所有数据都会被拉出并加载到内存中。如果您有5种不同的类型,则意味着该类型的每个页面加载的数据量是其需要的5倍。
Even with just one at a time, you are probably going to want to start paginating with LIMIT/OFFSET queries fairly quickly if you have more than 100 or however many you can reasonably display on one page at a time.
即使一次只有一个,如果你有超过100个,或者你可以一次合理地在一个页面上显示多少,你很可能会很快开始使用LIMIT / OFFSET查询进行分页。
#1
9
It depends what's more important: readability or performance. I'd expect a single query and prefilling PHP arrays would be faster to execute, since database connections are expensive, but then a simple query for each section is much more readable.
这取决于什么更重要:可读性或性能。我希望单个查询和预填充PHP数组执行起来会更快,因为数据库连接很昂贵,但是每个部分的简单查询都更具可读性。
Unless you know (and not just hope) you're going to get a huge amount of traffic I'd go for separate queries and then worry about optimising if it looks like it'll be a problem. At that point there'll be other things you'll want to do anyway, such as building a data access layer and adding some caching.
除非你知道(而不仅仅是希望)你将获得大量的流量,我会去单独查询,然后担心优化,如果它看起来像是一个问题。此时,您还需要做其他事情,例如构建数据访问层和添加一些缓存。
#2
2
If by "sections" you mean separate single pages (separate HTTP requests) that users can view, I would suggest query-per-type as needed. If on a page where there are only image data sets, you really don't need to fetch the video data set for example. You won't be really saving much time fetching everything, since you will be connecting to the database for every page hit anyway (I assume.)
如果“部分”是指用户可以查看的单独的单个页面(单独的HTTP请求),我建议根据需要使用每个类型的查询。如果在只有图像数据集的页面上,您实际上不需要获取视频数据集。你不会真正节省很多时间来获取所有东西,因为无论如何你将连接到数据库中的每一页(我假设。)
If by "sections" you mean different parts of one page, then fetch everything at once. This will save you time on querying (only one query.)
如果“部分”表示一页的不同部分,则立即获取所有部分。这将节省您查询的时间(只有一个查询。)
But depending on the size of your data set, you could run into trouble with PHP's memory limit querying for everything, though. You could then try raising the memory limit, but if that fails you'll probably have to fall back to query-per-type.
但是,根据数据集的大小,您可能会遇到PHP内存限制查询所有内容的问题。然后,您可以尝试提高内存限制,但如果失败,您可能不得不回退到每个类型的查询。
Using the query-per-type approach moves some of the computing load to the database server, as you will only be requesting and fetching what you really need. And you don't have to write code to filter and sort your results. Filtering and sorting is something the database is generally better at than PHP code. If at all possible, enable MySQL's query cache, that will speed up these queries much more than anything you could write in PHP.
使用每个类型的查询方法将一些计算负载移动到数据库服务器,因为您只需要请求并获取您真正需要的内容。而且您不必编写代码来过滤和排序结果。过滤和排序是数据库通常比PHP代码更好的东西。如果可能的话,启用MySQL的查询缓存,这将比在PHP中编写的任何内容更加加速这些查询。
#3
1
If your data is all coming from one table, I would only do one query.
如果您的数据全部来自一个表,我只会进行一次查询。
I presume you are building a single page with a section for pictures, a section for video, a section for music, etc. Write your query return results sorted by media type - iterate through all the pictures, then all the video, then all the music.
我假设您正在构建一个单页,其中包含一个图片部分,一个视频部分,一个音乐部分等。编写您的查询返回结果按媒体类型排序 - 遍历所有图片,然后是所有视频,然后全部音乐。
#4
0
Better to have multiple queries. Every time you run a query all the data is getting pulled out and loaded into memory. If you have 5 different types, it means each page of that type is loading 5 times as much data as it needs to do.
最好有多个查询。每次运行查询时,所有数据都会被拉出并加载到内存中。如果您有5种不同的类型,则意味着该类型的每个页面加载的数据量是其需要的5倍。
Even with just one at a time, you are probably going to want to start paginating with LIMIT/OFFSET queries fairly quickly if you have more than 100 or however many you can reasonably display on one page at a time.
即使一次只有一个,如果你有超过100个,或者你可以一次合理地在一个页面上显示多少,你很可能会很快开始使用LIMIT / OFFSET查询进行分页。