更高效 - 多个SQL查询或PHP中的一个查询和进程？

Question

I have a php application showing 3 tables of data, each from the same MySQL table. Each record has an integer field named status which can have values 1, 2 or 3. Table 1 shows all records with status = 1, Table 2 showing status = 2 and table 3 showing status = 3.

To achieve this three MySQL queries could be run using WHERE to filter by status, iterating through each set of results once to populate the three tables.

Another approach would be to select all from the table and then iterate through the same set of results once for each table, using php to test the value of status each time.

Would one of these approaches be significantly more efficient than the other? Or would one of them be considered better practice than the other?

Answer 1

Generally, it's better to filter on the RDBMS side so you can reduce the amount of data you need to transfer.

Transferring data from the RDBMS server over the network to the PHP client is not free. Networks have a capacity, and you can generate so much traffic that it becomes a constraint on your application performance.

For example, recently I helped a user who was running queries many times per second, each generating 13MB of result set data. The queries execute quickly on the server, but they couldn't get the data to his app because he was simply exhausting his network bandwidth. This was a performance problem that didn't happen during his testing, because when he ran one query at a time, it was within the network capacity.