Database Queries

  • bbarton

    (@bbarton)


    13 years ago

    Looking for opinions on which method is best.

    Lets say I want to query all Posts based on user id, and along with that I need all Comments associated with each Post of the query. I see two solutions (could be more).

    #1
    I take the result set of Posts query and loop through it, and for each Post I then query the Comments in database based on Post id.

    foreach($post as $key => $value){
			$comments = query database here
			$post[$key]->comments = $comments;
		}

    #2
    I query the Posts and query the Comments based on user id. Then loop through Posts and within that loop, loop through the Comments and compare the Comments Post id with the Post id.

    foreach($post as $key => $value){
			foreach($comments as $k => $v){
				if($v->comment_id == $value->id){
					$post[$key]->comments = $comments[$k];
				}
			}
}

    I looking for the less expensive way for server load/bandwidth. More queries vs 2 larger queries(All Posts & Comments -> User)?

Viewing 1 replies (of 1 total)
  • Moderator bcworkz

    (@bcworkz)

    13 years ago

    Interesting question. Either way returns the same amount of data, one with way more queries than the other. The first way, mySQL must search through a lot of data to return a small amount of data with each query.

    The second way PHP must search through a lot of data to return a small amount of data for each iteration, though in this case the amount of data to search through is smaller than mySQL’s data. Despite this, mySQL is probably more efficient because the DB is set up for this sort of searching. PHP stepping through arrays is quite crude in comparison.

    But there’s a lot more queries made for mySQL to do the searching. While there’s a lot more exchanges, the actual increase in bandwidth is small because queries are very compact in comparison to the data returned.

    I’ve no evidence to back this up, but I think letting mySQL do the searching is more efficient, despite the increase in queries. It takes the load off PHP so the server can do other things it is better at while mySQL is doing it’s thing. IMHO of course.

Viewing 1 replies (of 1 total)

The topic ‘Database Queries’ is closed to new replies.