Multiple queries or additional array processing?

Question

I have a table that looks somewhat like this:

CREATE TABLE foobar
(
    id INT(6) NOT NULL AUTO_INCREMENT,
    category ENUM('Cat1','Cat2','Cat3','Cat4') NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY(id),
);

I have one page where all this data is to be displayed like this:

<h3>Cat1</h3>
<ul>
  <li>Value1</li>
  <li>Value2</li>
</ul>


<h3>Cat2</h3>
<ul>
  <li>Value1</li>
  <li>Value2</li>
</ul>

...

My question is whether it's more efficient to make one query for each category limiting the result set to one category... SELECT name FROM foobar WHERE category = cat1; or whether I should get the entire table SELECT category, name FROM foobar; and then use PHP to process the results and then loop through each category and display its values.

Which is more effcient or is there another way to do this?

Thanks!

It's *usually* more efficient to run fewer queries, but you should test both methods and see which is better in your case. Most likely the single query will be better, if you're simply dumping out the entire table. — Marc B, May 13 '11 at 16:16
Can you post up the output of `SELECT category, COUNT(category) FROM foobar GROUP BY category` and give an indication of whether that number (and distribution) is likely to change? — James C, May 13 '11 at 16:19
@james-c right now there are less than 100 items in that particular table. The distribution is approx. 25/25/20/30. This list will grow in the future though. — NightHawk, May 13 '11 at 16:25

score 0 · Accepted Answer · edited May 23 '17 at 11:55

0

In most cases you want to do the latter. Hitting the database multiple times consumes a lot of extra network bandwidth, and you'll end up looping on the PHP side anyway.

This is similar to the N+1 query problem, which you can read more about on StackOverflow or elsewhere.

For example: What is SELECT N+1?

edited May 23 '17 at 11:55

Community

1
1

answered May 13 '11 at 16:19

Paul Rosania

9,823
2
20
18

score 0 · Answer 2 · answered May 13 '11 at 16:19

0

Your case is very common and people uses single query in this case because they want to reduce talk with database and fetch all the data in once only if all data is required in once and that is true in your case.

Use single query.

answered May 13 '11 at 16:19

Shakti Singh

84,385
21
134
153

Multiple queries or additional array processing?

2 Answers2