Question

首先,我没有控制数据库结构等。

我需要使用PHP,以逐国和国名检索记录,但所有数据都在多个表格中。基本上,我需要尝试和理解如何把这两个问题结合起来,以便它们不会陷入如此缓慢的境地。

First I get my record ID s in PHP. (Lets assume $query returns an array of IDs)

表_products = Abunch of products

$query = "SELECT id,name FROM table_products WHERE name =  ".$name." ;";

Than I need to iterate through these records (NOTE): 可在另外两个表格中注明这些身份证的所在地点。

表_ 地点 = 配备一栋楼梯的桌子

链接-表 = 保持产品与地点之间的关系

$state = "somestate";

foreach($query as $row)
    {
    $query_two = "SELECT table_places.name, table_places.id, table_places.state, link_table.place_id, link_table.product_id 
    FROM table_places 
    INNER JOIN link_table 
    ON table_places.id = link_table.place_id 
    WHERE table_places.state =  ".$state."  AND link_table.product_id =  ".$row->id." ;";
    }

上帝希望这一点变得有意义。我不问,如果我能够获得援助,使这一努力能够更快地运作,我将不胜感激。

Answer 1

痛苦在这里是:

foreach($query as $row)   <<--- you are pinging the DB to death.

回答两个问题:

SELECT 
  pl.name, pl.id, pl.state, 
  l.place_id, l.product_id,
  pr.name   
FROM table_places pl  
INNER JOIN link_table l ON (pl.id = l.place_id)  
INNER JOIN table_products pr ON (l.product_id = pr.id)
WHERE pr.name =  $name 
  AND pl.state =  $state 
ORDER BY pr.name, pl.state

确保对<代码>中所有使用领域的索引 http://www.un.org/Depts/DGACM/index_chinese.htm

Answer 2

You can join more than two tables in one query. Untested query would be something like;

SELECT * FROM table_products AS prod LEFT JOIN (link_table AS link, table_places AS places)
    ON (prod.id=link.id AND prod.id=places.id)
WHERE some_field= some_value

这肯定会提高你们的绩效,因为一个查询的频率比s/em>记录号码要快得多。问询(因为你现在通过记录,每记录一次查询。)

Answer 3

The answer is simple: no control over database structure - no way to optimize.
As the indexing being the cornerstone of query optimization and you obviously need access to table structure to add one.

友情链接