SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subquery or nested queries?

    Which is better based on performance? I wrote the following subquery for optimization and to reduce a LOT of queries on a page because of loops. However, my partner who is also a developer argues that its just as same as the nested query and maybe even slower.

    My Subquery:
    $sql_aen = mysql_fetch_assoc("SELECT `values`, `mem_id`, `mem_source`, (SELECT `values` FROM `nu_users` WHERE `memr_id`=`mem_source` LIMIT 1) AS `src_values`, (SELECT `mem_type` FROM `nu_users` WHERE `mem_id`=`mem_source` LIMIT 1) AS `src_type` FROM `nu_users` WHERE `mem_id`='kouski' LIMIT 1");

    $sql_values = $sql_aen['values'];
    $sql_svalues = $sql_sen['src_values'];
    $sql_stype = $sql_aen['src_type'];
    Original nested queries:

    $sql_aen = mysql_fetch_assoc(mysql_query("SELECT `values`, `mem_id`, `mem_source` FROM `nu_users` WHERE `mem_id`='koiski' LIMIT 1"));
    $sql_sen = mysql_fetch_assoc(mysql_query("SELECT `values`, `mem_type` FROM `nu_users` WHERE `mem_id`='" . $sql_aen['mem_source'] . "' LIMIT 1"));

    $sql_values = $sql_aen['values'];
    $sql_svalues = $sql_sen['values'];
    $sql_stype = $sql_aen['mem_type'];
    Thanks in advance.

  2. #2
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    1 Thread(s)
    Typically subqueries will be faster but less portable - there's more than a few MySQL 4.0 servers out there still which can't handle subqueries. This becomes especially true if the first result must transfer a large chunk of data to PHP.

    But the only real answer is to take a timestamp before and after the query runs and time it. Take five samples and use the average (this is to account for any other processes on the box that might be slowing things down).

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well from what it seems both seem to be pretty much the same more or less. My query just saves one out of 7 queries otherwise needed. The problem is once the loop goes half way its already runs around 120 queries *sigh* I'm trying my best to get it down to at least 60-80 but I cant change all the queries without being sure.

    What would you opt for in general situations I mean...?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in post #1, the two separate queries are not actually nested, and their results is ~not~ the same as the results of the single query with the subqueries

    i would use the two separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Without the code, it's hard to say how to optimize your queries. For example, are you sure you have to do your queries inside a loop? Can't you extract all data with a couple of queries and then loop through the result sets?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •