Hey, I want to find out which is faster: - Making 1 query + retrieving a lot of data (say 200,000 rows) then sorting through it using php. - Making several queries but only retrieving the data you need (say 10 queries each returning 10 rows). Does anybody know where to find info like this? Thanks!
Also if you are using php or another web application to perform the querying there can be a lot of other factors that play into how fast the script executes. One easy way to benchmark sections of scripts is to calculate the time that each section takes to execute. You can then adjust your code and test more to see which method is faster. For php the microtime() function comes in handy.
Ah yes, microtime is really helpful! I've been trying 2 ways to do the same thing here are the stats: Method 1: time = 0.010710954666138 queries = 88 fetches = 116 Method 2: time = 0.2459352016449 seconds queries = 57 fetches = 62474 Looks like fewer fetches wins by a long shot in this case! It's a shame I can't figure out how to cut down on the 88 queries though :S anyone know if 0.01s a long time to be querying a db? (for reference I often get 100k pageviews a day).
Hi okgaz, a well designed query coupled with an efficient db structure is always going to be quicker than putting a lot of results through the PHP mill. The question I'd really be asking here is why you're even making 8 queries, not 88! If this process is something that your visitors do regularly and you're getting 100k hits a day then your db should be optimised to provide these results and having to do 88 queries doesn't sound very optimal. Can you give us any more info on the nature of the data and/or site? It might help to make more constructive advice... Jon
The reason it uses so many queries is because it's pretty difficult + I'm still an SQL rookie. I'll happily pay somebody who can come up with an elegant solution to the problem/rework my databases to make it work better! Basically I have a high score section in my website and am making a page that will display a users score and some stats for each game like so: Game Name 1 | Score | Position | Out of Game Name 2 | Score | Position | Out of etc. There's 29 games atm (will be more in the future). So I'm making 1 query + 29 fetches to get info about all of the games. Then I'm looping through each game with a query to get the users score in it (another 29 queries / fetches). Then for each of these, if a score exists I make a query to see how many people have a score greater than it and a seperate query to see the total number of users who have a score for that game (a ton more queries / fetches!). If any1 has a good idea how to do this kind of thing more efficiently drop me a PM, I can pay you for your time + there's the possibility of future work.