Which is faster for three tables, mysql or a custom php function

[brown2005]

I have three tables that I can link up via mysql and get the results I need, but I could also query just the one table and get the info from the other two via a function, my question is which is the faster and/or better way and why?

 

Thanks

[trq]

Getting the data you want through an actual query is always going to be faster than having php do the grunt work.

[Crashthatch]

Generally you'll find that doing it with a single query with JOINs is faster because it's less round-trips to the database. Each mySQL query has an overhead cost (a fixed time latency between the PHP and mySQL server, say 0.01 second), so doing 3 queries takes 3*0.01 + mySQL processing time (say, 0.0001 second). = 0.0301 seconds.

 

Doing 1 query will take longer to do the processing, but saves you massive amounts on the overhead costs:

1*0.01 + longer mySQL processing time (say, 0.0005 seconds) = 0.0105 seconds.

 

I've made up the numbers here to illustrate my point, but unless you're running a very complicated query, the processing time will be less than the overhead of making a query. The numbers here also ignore the extra processing you'd have to do in PHP in the first case.

 

There are probably times that doing stuff in PHP could be faster, but they're so uncommon it's not worth worrying about. Always try to do the minimum amount of queries.

[shlumph]

Depends if you're utilizing some sort of result set cache in the PHP functions. If not, as others have mentioned, it'd be faster using a join, especially if there's multiple users accessing the site. There'll be 3x less queries.