SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jul 2009
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql Performance?

    Hi, I'm wondering if breaking up the query into 2 or 3 queries will have any impact on the performance?

    Techinically I could combine them into 1 statement but, it makes the query complicated and I suspect mysql is going through the same number of data (since the number of rows and cols in the tables is always the same regardless of the queries made).

    To make things clear, heres an example:
    //First statement to find a child value and store to a variable
    $child = SELECT child FROM parent WHERE child = 1; //syntax is wrong, just for demostration

    //THEN you find siblings with that value
    $siblings = SELECT silbings FROM children WHERE child = $child

    OR...

    //Combining the statements
    $siblings = SELECT silbings FROM children WHERE child = (SELECT child FROM parent WHERE child = 1)

    Does the 2nd method performs better? It seems to me both reads the same amount of data.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    It won't make that much of a difference, especially if MySQL if located on he same server. Sometimes it is best to break things up just create more reusable code. This is especially true when dealing with the adjacency model. In that case your always forced to run separate queries for each node. if its for something like navigation though which is needed every request then caching after the hierarchy has been built once is a good optimization solution. Seems like what you might be attempting to do so thought I would offer up the advice.

  3. #3
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Am I missing something here, or can't you get the same thing like this:
    Code:
    SELECT silbings FROM children WHERE parent = 1;
    I mean you already have the parent.

    Or, if you mistyped your sql:
    Code:
    SELECT silbings FROM children WHERE parent IN (SELECT parent FROM children WHERE child = 1);
    A kid can have 2 parents...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by resting View Post
    Hi, I'm wondering if breaking up the query into 2 or 3 queries will have any impact on the performance?
    in general, it will make the performance worse

    your child examples aren't really very good, there's nothing there to suggest breaking up the query (if i could understand your query)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jul 2009
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorri if the example confused you guys. it was a quick one just to demostrate what I'm trying to achieve. its not a real world example.

    but I was thinking. aren't queries kind of like filters?
    Despite the WHERE statement returning only a few rows, it has to scan through the full table to select the correct data?

    So I was thinking if there will actually be a speed difference between joining 2 tables together and filtering, and doing 2 small filters.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in general, you should not try to outthink the database optimizer

    it's a lot smarter than you think it is, and it's way smarter than you are

    no offence
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •