PHP / MySQL：大规模SQL查询还是几个较小的查询？

Question

I have a database design here that looks this in simplified version:

Table building:

• id
• attribute1
• attribute2

Data in there is like:

• (1, 1, 1)
• (2, 1, 2)
• (3, 5, 4)

And the tables, attribute1_values and attribute2_values, structured as:

• id
• value

Which contains information like:

• (1, "Textual description of option 1")
• (2, "Textual description of option 2")
• ...
• (6, "Textual description of option 6")

I am unsure whether this is the best setup or not, but it is done as such per requirements of my project manager. It definitely has some truth in it as you can modify the text easily now without messing op the id's.

However now I have come to a page where I need to list the attributes, so how do I go about there? I see two major options:

1) Make one big query which gathers all values from building and at the same time picks the correct textual representation from the attribute{x}_values table.

2) Make a small query that gathers all values from the building table. Then after that get the textual representation of each attribute one at a time.

What is the best option to pick? Is option 1 even faster as option 2 at all? If so, is it worth the extra trouble concerning maintenance?

Answer 1

Never do one at a time queries, try to combine them into a single one.

MySQL will cache your query and it will run much faster. PhP loops are faster than doing many requests to the database.

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

http://dev.mysql.com/doc/refman/5.1/en/query-cache.html