MySQL querry question?

Question

I have two choices for running a query and I'm curious to know which would be more efficient. And if there wouldn't be any significant difference in efficiency, is one more preferable to the other?

The first would return a query that would return a small number of rows and then I would run a sub query while processing each row which would return as many as 20 additional rows for each row in the first query. The first query would return about 10 rows with about 15 columns each, the second query would return about 20 rows with 4 columns for each row in the first query.

The second option would be to run 1 query which would return several hundred rows and break it up using loops within the my PHP script. Each of the several hundred rows is going to contain about 20 columns.

If I use the second option, each row is going to contain all the data from both queries.

Answer 1

well i think the first choices is better, mysql process is fater than PHP, but you can test that buy using page request time , you used both ways and you see which one retrive data faster than other.

php looping take time, stay away from loop as much you can

Answer 2

Single query returning several hundred rows is actually going to be much more efficient. Each query in a Database reads from Disk. So in option 1 you are going to be doing many disk reads to retrieve the necessary result. Using option 2 you will read from disk 1 time and process the resulting dataset in memory.

Answer 3

im using MSSQL, but have some experience programming using MySQL.. the functionality you're needing can be
done inside a sproc (in mssql) where you declare a variable
table, put the records in that var table from the result of your
first query, and then use that var table to query the second resultsets.. there is no need to for loop or retrieve all the data..

as i said, it can be done inside a sproc.. and i think you can do that in MySQL 5.0, because it supports sproc..

hope this helps.. peace out ^_^

Answer 4

I would go with the first option since it would be easy to maintain and I believe it would be faster as well. First option is good because your web server is not processing hundreds of records and in case if you have hundred of users running same kind of queries then you will not be using alot of server resources. Second option, with hundred of users can get your server to respond slow.

Answer 5

For maintenance reason, first option is better....
As for saving time and space - second option is the pick, plus the data set will be broken into smaller group.