Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: php/mysql - too many queries

    I think I might be performing my queries in a pretty bad way.

    The following code works - however if my result set is say, 80 rows then it performs about 100 queries each time its run.

    I thought using mysql_fetch_assoc would get around that problem, but now Im guessing my query might not be as optimised as it could be.

    Am I doing something major league wrong?

    Any pointers appreciated.

    Thanks,

    PHP Code:
    $ItemDetail db_query("SELECT
                             
    {$db_prefix}clinic.Name
                            ,
    {$db_prefix}clinic_reviews.id
                            ,
    {$db_prefix}clinic_reviews.summary
                            ,
    {$db_prefix}clinic_reviews.rating
                            ,
    {$db_prefix}clinic_reviews.review
                            ,
    {$db_prefix}clinic_reviews.reviewdate
                            ,
    {$db_prefix}clinic_reviews.clinicid
                            ,
    {$db_prefix}members.memberName
                            ,
    {$db_prefix}clinic_reviews.MEMBER_ID
                            
                            FROM
                            
    {$db_prefix}clinic
                            INNER JOIN 
    {$db_prefix}clinic_reviews 
                                ON (
    {$db_prefix}clinic.ClinicID = {$db_prefix}clinic_reviews.clinicid)
                            INNER JOIN 
    {$db_prefix}members 
                                ON (
    {$db_prefix}clinic_reviews.MEMBER_ID = {$db_prefix}members.ID_MEMBER) where {$db_prefix}clinic_reviews.clinicid={$clinicid} ORDER by {$db_prefix}clinic_reviews.id desc ",__FILE__,__LINE__);
            
            

    $context['summaryview'] = array();

    $i=0;
    while (
    $sumdetail_row mysql_fetch_assoc($ItemDetail))

                    { 
                        
    $context['summaryview'][$i] = array(
                        
    'reviewid'    => $sumdetail_row['id'],
                        
    'Name'        => $sumdetail_row['Name'],
                        
    'summary'     => $sumdetail_row['summary'],
                        
    'rating'         => $sumdetail_row['rating'],
                        
    'review'         => $sumdetail_row['review'],
                        
    'reviewdate'     => $sumdetail_row['reviewdate'],
                        
    'clinicid'         => $sumdetail_row['clinicid'],
                        
    'memberName' => $sumdetail_row['memberName'],
                        
    'member_ID' => $sumdetail_row['MEMBER_ID'],
                        );
                    
                    
    $i++;
                    }

    mysql_free_result($itemDetail); 

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    At first glance it looks fine to me. How many rows do you expect your query to return? Also, how do you know it's running 100 times? Have you managed to profile it? Have you functionised it and are calling something nasty like a recursive loop?

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Whats with the following :

    ,__FILE__,__LINE__);

    and how does the db_query utilise these parameters?
    If possible can you give us the code for the db_query function, that is probably your issue

  4. #4
    Join Date
    Aug 2007
    Posts
    3
    Hi,

    Thank you for taking the time to look at this - its much appreciated.

    1) The __FILE__,__LINE__ is to assist with error reporting.
    Some info on it here:
    http://www.userscape.com/blog/index....file_and_line/

    Im writing a modification for a forum system (SMF) - the dbquery is part of the forums subsystem.
    http://support.simplemachines.org/fu...n;function=247

    When a page is generated through the forum - the amount of queries used is displayed at the bottom.

    An example is shown here (very bottom left of page)
    http://www.simplemachines.org/community/

    I'm not sure how it profiles the number of queries.

    Today I noticed that this same query which was generating 80 queries has actually changed down to 8 !!! so I am now wondering if caching has kicked in and taken the stress out of what I thought was a problem.

    I'll play with it some more - but I think we might be out of the woods

    Thanks for taking the time to reply

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It's probably also worth setting up some indexes on your tables if you haven't already. They will speed up your query exponentially.

    And I just wanted to clarify something : when you say "80 queries" do you mean 80 row? Or is MySQL actually performing 80 separate queries?

    Also: how are you measuring this?

Posting Permissions

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