dongrou839975
2013-02-27 16:40 阅读 22
已采纳

我应该对列表和详细视图使用1或2个SQL查询吗?

Here is what bothers me: Is it more elegant (amount of data handled vs. number of queries) to use 1 or 2 queries.

let's use the following bogus structure:

CREATE TABLE myobj (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(64),
  color varchar(64) DEFAULT NULL,
  price float,
  description text,
  dateofcreation varchar(8) DEFAULT NULL
  PRIMARY KEY (id)
) 

I want to display a page with a list of records (object name, color, price)

List (name, color, price)
      apple, red,    5.00
      brick, orange, 1.00
      corn,  yellow, 2.00

and if $_REQUEST['id'] is defined, I'd like to display the total amount of details (name, color, price, description, date of creation) for the record whose id has been selected.

Finally the question:

Is it faster to use 1 query for the list view

SELECT name, color, price FROM myobj

followed with 1 query for the detailed view

SELECT * FROM myobj WHERE id=[id_provided]

or

1 query in total

SELECT * FROM myobj

and then save the values for the detailed view in PHP when the id of the current line of the result set matches the id provided in the http request?

I believe that 2 queries are more elegant but I wonder how efficient it is in terms of processing time (multiple db connections vs. heavy resultset including useless data)?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • 已采纳
    duanjiangzhi6851 duanjiangzhi6851 2013-02-27 16:45

    I think this really depends on many things, the number of rows and their size.

    Two calls has significant overhead. But depending on the size of the extra data that may or may not have more impact than the size the description field. If there was no "text" description field then one call would clearly be faster.

    But you said this was bogus -- so who can say about your actual query.

    点赞 评论 复制链接分享
  • dongling2038 dongling2038 2013-02-27 16:46

    It depends how much data you need.

    Generally - less queries is better.

    Lets say, there are "master" and "detail" tables.

    If you have 100 "master" rows - you will need 100 queries to get "details" for them.

    In cases like this - im getting all data with joins, then i build something like tree structure (array) to display it with "foreach" loop.

    点赞 评论 复制链接分享
  • douxuqiao6394 douxuqiao6394 2013-02-27 16:47

    Lets look at this. Your first suggestion for the list view is

       SELECT name, color, price FROM myobj
    

    This is going to be a full table scan, just the same as a SELECT * FROM myOBJ In other words, it's o(n)

    Next, you have SELECT * FROM myobj WHERE id=[id_provided] Since this field(id) is indexed, it's complexity is constant. It will just retrieve that row immediately.

    However, it looks like you're going to be showing the detail for all of your items, and I'm assuming that you're going to be iterating through all of your rows and getting the detail for each. A constant retrieval for n records is going to be O(n).

    If you're getting the detail for all the records, why not just select * once?

    点赞 评论 复制链接分享
  • dtdsbakn210537 dtdsbakn210537 2013-02-27 17:12

    This is a user interface question. Are you going to render the three columns first and then get the details? If so, then you want to minimize the time to the rendering. This says to use two queries. The simpler query should be slightly faster, because it is returning less data.

    If you are going to put all the details in before anyone sees them, then go with the second form. That seems unlikely.

    More importantly, I think it is bad practice to use * in queries of this sort. You should explicitly include the list of columns for both queries.

    点赞 评论 复制链接分享

相关推荐