您的位置: 首页  >  技术问答  >  从PHP优化数据库查询

从PHP优化数据库查询

分类: 技术问答 • 2022-03-17 11:55:43

从PHP优化数据库查询

问题描述：

I'm building a site Pinterest like-style, is in a very early stage of development.

I can do it without any problems, but I'm wonder to know if it is the best way.

Objective:

Extract all image data from the database, then, for each, extract data from the table "favorites", count the favorites of the photo, and then, if there is an active user session, whether the user marked as favorite.

Problem

The site shows by default 54 photos, then, for those 54 photos, the program must to look the favorites for each. This represents many requests to the server and the database, I have thought it of the form:

$images = 'SELECT * FROM IMAGES photo_id ORDER BY LIMIT 54';

foreach ( $images as $image ) {
   $lookFavorite = 'SELECT * FROM favorites WHERE photo_id ='. $ image ['photo_id'] ';
}

Database tables structure

Photos

photo_id    INT (auto increment)
user_nick   VARCHAR (25)
photo_path  VARCHAR (255)
photo_title VARCHAR (150)
photo_theme VARCHAR (60)
date        TIMESTAMP

Favorites

photo_id  INT
user_nick VARCHAR (25)
date      TIMESTAMP

Important

PHP version 5.3, using MySQL.
The program I'm building is object oriented [I'm using PDO'].

我正在构建一个类似Pinterest的网站，处于发展的早期阶段。 p>

我可以毫无问题地做到这一点，但我很想知道它是否是最佳方式。 p>

目标： h1>

从数据库中提取所有图像数据，然后为每个图像数据从“收藏夹”表中提取数据，计算照片的收藏夹，然后，如果存在活动用户会话，则将用户标记为收藏夹。 p>

问题 h1>

网站默认显示54张照片，然后，对于这54张照片，程序必须查看每张照片的收藏夹。 这 表示对服务器和数据库的许多请求，我认为它的形式如下： p> 

  $ images ='SELECT * FROM IMAGES photo_id ORDER BY LIMIT 54'; 
 \  nforeach（$ images为$ image）{
 $ lookFavorite ='SELECT * FROM favorites WHERE photo_id ='。  $ image ['photo_id']'; 
} 
  code>  pre> 
 
 
数据库表格结构 h1> 
 
 
照片 h2> 
  
 
  photo_id INT（自动增量）
user_nick VARCHAR（25）
photo_path VARCHAR（255）
photo_title VARCHAR（150）
photo_theme VARCHAR（60）
date TIMESTAMP 
  code>  pre  > 
 
 
收藏夹 h2> 
 
 
  photo_id INT 
user_nick VARCHAR（25）
date TIMESTAMP 
  code>  pre> 
 
 
重要 h1> 
 
 
  PHP版本5.3，使用MySQL。
我正在构建的程序是面向对象的[我正在使用PDO']。
  code>   pre> 
  div>

答

drop the loop and do

$lookFavorite = 'SELECT * FROM favorites';

create an array of everything, and then cycle through what you need from that array based on what you get from

$images = 'SELECT * FROM IMAGES photo_id ORDER BY LIMIT 54';

this way, there are only two queries ran instead of 55

or

$images = 'SELECT * FROM photos INNER JOIN favorites ON (favorites.photo_id = photos.photo_id) ORDER BY favorites.photo_id LIMIT 54';

to do it in a single query

答

You should use a Left join From Images to Favorites and then use GROUP BY's as needed to get your counts in case there are images that have 0 favorites.

SELECT * 
FROM `images` 
LEFT JOIN `favorites` ON `images`.`photo_id`=`favotites`.`photo_id`

答

I think what you are looking for here is a LEFT JOIN.

SELECT * FROM images
LEFT JOIN favorites
ON images.photo_id = favorites.photo_id
WHERE images.photo_id IN (..array populated from first query..)

If you need to limit your results to just the first 54 images then you can just populate the WHERE clause of the JOIN with the appropriate predicate.

As a side note, I chose LEFT JOIN because I want to include all images in that list even if they have no favorites, but I want to not include favorites that are not relevant to the images I want.

答

1 do not use * if you dont need all fields.

test on you server, but i think this solution work faster 

$images = 'SELECT photo_id, photo_path FROM photos LIMIT 54';
$ids = array();
foreach ( $images as $image ) {
  $ids[]= $image ['photo_id'];
}

$lookFavorite = 'SELECT * FROM favorites WHERE photo_id in ('. implode(',',$ids). ')';

相关推荐