Hopefully this is not too subjective, but:
希望这不是太主观,但是:
I have a problem where I need to query data from four different tables based on an ID. This data has similar but not identical fields that need to be displayed for each table. Essentially, if I have tables A, B, C, and D, then for an id N, I might need to display rows in the following order:
我有一个问题,我需要根据ID查询来自四个不同表的数据。此数据具有类似但不相同的字段,需要为每个表显示。基本上,如果我有表A,B,C和D,那么对于id N,我可能需要按以下顺序显示行:
A1
B1
B2
B3
C1
D1
D2
D3
D4
However, since some of B and D requires that I join to many tables, and I have tens of thousands of records, I don't want to be sending four complex queries for every id. I was of pre-building my data - creating a temporary table that houses each of the final A,B,C,D data and then querying directly by ID to those rows. Is there a faster way? I've thought of using a UNION but my rows don't all have the same kinds of columns (for rows of type A I might need to display a date, while rows of type C will have an integer day offset, for example).
但是,由于B和D中的一些要求我加入很多表,并且我有成千上万的记录,所以我不想为每个id发送四个复杂的查询。我正在预先构建我的数据 - 创建一个临时表,其中包含每个最终的A,B,C,D数据,然后通过ID直接查询这些行。有更快的方法吗?我曾想过使用UNION但我的行并不都具有相同类型的列(对于类型A的行,我可能需要显示日期,而类型C的行将具有整数日偏移量,例如)。
My current approach:
我目前的做法:
foreach ($ids as $id) {
$a = "SELECT * FROM a JOIN xyz (...) WHERE id = ?";
$b = "SELECT * FROM b JOIN xyz (...) WHERE id = ?";
$c = "SELECT * FROM c JOIN xyz (...) WHERE id = ?";
$d = "SELECT * FROM d JOIN xyz (...) WHERE id = ?";
print query($a, $id)->fetch();
print query($b, $id)->fetchAll();
print query($c, $id)->fetch();
print query($d, $id)->fetchAll();
}
Or something like that
或类似的东西
1 个解决方案
#1
1
First of all, I definitely wouldn't query the DB 4 times. I would try to query the DB only once, even if it requires nasty and long sql code, in order to reduce round trips between the server and DB.
首先,我肯定不会4次查询数据库。我会尝试只查询一次DB,即使它需要讨厌和长的SQL代码,以减少服务器和数据库之间的往返。
Just like "Consider Me" and "Gordon Linoff" in the comments I would create a single query with all the fields from all tables and UNION them all. For fields exclusive for some tables I would put 'null' when declaring them in queries for other tables.
就像评论中的“考虑我”和“戈登·林诺夫”一样,我将使用所有表中的所有字段和UNION创建单个查询。对于某些表独有的字段,我会在查询其他表时将其设置为“null”。
For example:
select t.description as 'Description', null as 'Value'
from A t
UNION
select null as 'Description', t.value as 'Value'
from B t
You can also return ALL fields of all tables and prefix each returned field so you'd know from which table it originated.
您还可以返回所有表的所有字段并为每个返回的字段添加前缀,以便您知道它来自哪个表。
For example:
select t.description as 'A_Description'
from A t
UNION
select t.value as 'B_Value'
from B t
For simplicity, security and efficiency I would put the whole sql code inside a user-defined function that returns a table which you can interpret from the server side code as a recordset. The function would have the ID as a parameter. This can also be achieved in a form of a view.
为了简单,安全和高效,我将整个sql代码放在一个用户定义的函数中,该函数返回一个表,您可以将其从服务器端代码解释为记录集。该函数将ID作为参数。这也可以以视图的形式实现。
For efficiency, I would put index on every field that the tables use for join, this should decrease the query time substantially.
为了提高效率,我会将索引放在表用于连接的每个字段上,这应该会大大减少查询时间。
In general, I would also try to find out how many times this function is called in a day / hour / minute / sec. If the data provided by the function is needed frequently, than maybe a whole different approach is needed - rearranging the whole DB. However, if the data is static or semi-static then maybe a caching mechanism is sufficient - in a form of another DB table, server side static object or even a client side static object.
一般来说,我也会尝试找出每天/小时/分钟/秒调用此函数的次数。如果频繁需要该功能提供的数据,则可能需要采用完全不同的方法 - 重新安排整个数据库。但是,如果数据是静态的或半静态的,那么可能的缓存机制就足够了 - 以另一个DB表,服务器端静态对象甚至客户端静态对象的形式。
There isn't a simple answer to your question as there are multiple factors to take under consideration.
您的问题没有一个简单的答案,因为有许多因素需要考虑。
#1
1
First of all, I definitely wouldn't query the DB 4 times. I would try to query the DB only once, even if it requires nasty and long sql code, in order to reduce round trips between the server and DB.
首先,我肯定不会4次查询数据库。我会尝试只查询一次DB,即使它需要讨厌和长的SQL代码,以减少服务器和数据库之间的往返。
Just like "Consider Me" and "Gordon Linoff" in the comments I would create a single query with all the fields from all tables and UNION them all. For fields exclusive for some tables I would put 'null' when declaring them in queries for other tables.
就像评论中的“考虑我”和“戈登·林诺夫”一样,我将使用所有表中的所有字段和UNION创建单个查询。对于某些表独有的字段,我会在查询其他表时将其设置为“null”。
For example:
select t.description as 'Description', null as 'Value'
from A t
UNION
select null as 'Description', t.value as 'Value'
from B t
You can also return ALL fields of all tables and prefix each returned field so you'd know from which table it originated.
您还可以返回所有表的所有字段并为每个返回的字段添加前缀,以便您知道它来自哪个表。
For example:
select t.description as 'A_Description'
from A t
UNION
select t.value as 'B_Value'
from B t
For simplicity, security and efficiency I would put the whole sql code inside a user-defined function that returns a table which you can interpret from the server side code as a recordset. The function would have the ID as a parameter. This can also be achieved in a form of a view.
为了简单,安全和高效,我将整个sql代码放在一个用户定义的函数中,该函数返回一个表,您可以将其从服务器端代码解释为记录集。该函数将ID作为参数。这也可以以视图的形式实现。
For efficiency, I would put index on every field that the tables use for join, this should decrease the query time substantially.
为了提高效率,我会将索引放在表用于连接的每个字段上,这应该会大大减少查询时间。
In general, I would also try to find out how many times this function is called in a day / hour / minute / sec. If the data provided by the function is needed frequently, than maybe a whole different approach is needed - rearranging the whole DB. However, if the data is static or semi-static then maybe a caching mechanism is sufficient - in a form of another DB table, server side static object or even a client side static object.
一般来说,我也会尝试找出每天/小时/分钟/秒调用此函数的次数。如果频繁需要该功能提供的数据,则可能需要采用完全不同的方法 - 重新安排整个数据库。但是,如果数据是静态的或半静态的,那么可能的缓存机制就足够了 - 以另一个DB表,服务器端静态对象甚至客户端静态对象的形式。
There isn't a simple answer to your question as there are multiple factors to take under consideration.
您的问题没有一个简单的答案,因为有许多因素需要考虑。