SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    optimize the query

    In the below code how can I optimize the query.
    Yhe fieldname has more than 3 rows for the particular id.

    The while loop takes longer time to open the page if there are more records.

    So how can I optimize the query so that speed increases


    Code:
    $result = mysql_query("select * from table1");
                    $no_of_rows += mysql_num_rows($result);
            while($r = mysql_fetch_array($result)){
                    $fieldname = '';
                    $res = mysql_query("select distinct(filed) from table2 where id='".$r[0]."'");
                    
                    while($row = mysql_fetch_array($res)){
                       $fieldname .= $row[0]."\n";
                            }
                    
                                    array_push($records, "$r[0]#$r[1]#$r[2]#$r[3]#$r[4]#$r[5]#$fieldname");
                    }

  2. #2
    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)
    you can optimize this by getting rid of the 2nd query inside the loop
    Code:
    SELECT table1.* 
         , d.*
      FROM table1
    INNER
      JOIN ( SELECT id
                  , filed 
               FROM table2 
             GROUP
                 BY id
                  , filed ) AS d
        ON d.id = table1.id
    note that DISTINCT is not a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you can optimize this by getting rid of the 2nd query inside the loop
    Code:
    SELECT table1.* 
         , d.*
      FROM table1
    INNER
      JOIN ( SELECT id
                  , filed 
               FROM table2 
             GROUP
                 BY id
                  , filed ) AS d
        ON d.id = table1.id
    note that DISTINCT is not a function

    select * from tablename where id='172017';
    +------------------------------+---------------------+--------------+--------------------------------+
    | Name | timestamp | id | fieldname4 |
    +------------------------------+---------------------+--------------+--------------------------------+
    | A | 2008-07-31 15:20:01 | 172017 | AAA/0 |
    |A| 2008-07-31 15:20:01 | 172017 | BBB/1 |
    +------------------------------+---------------------+--------------+--------------------------------+

    Actually the row fetched is like this. I have to display in the web page as

    Single Name and in fieldname4 2 rows(
    NAme Filedname
    A AAA/0
    BBB/1

    In the single <td>

    How can i do that

  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)
    what i think you want is:
    Code:
     Name  Fieldname
      A    AAA/0 
           BBB/1
    with spaces in the 2nd row where the name was

    suppressing the "repeating" name is not done with sql, this is done with your application logic

    you still need only one single query with a join
    rudy.ca | @rudydotca
    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
  •