CODEWITHSUNDEEP
phpmysqldatabase
Smithy
Smithy

Reputation: 405

More efficient - multiple SQL queries or one query and process in php?

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?

Upvotes: 4

Views: 2859

Answers (3)

peterpeterson
peterpeterson

Reputation: 1325

I would create a store procedure that return all the fields you need pre-formatted, no more, no less.

And then just loop on php without calling any other table.

This way you run only 1 query, and you only get the bytes you need. So same bandwidth, less http request = more performance.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562921

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.

Upvotes: 2

Zbyszek Kisły
Zbyszek Kisły

Reputation: 2248

If you use the second method you connect with database only once, thus it's more efficient. And even if it wasn't, it's more elegant that way IMO.

Of course there are some situations that it would be better to connect three times (eg. getting info from this query would be complicated), but for most of the cases I would do it the second way.

Upvotes: 0

Related Questions