SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow query on a large table

    Hello,

    I have a ticket-support system, the table of replies has more than 190,000 rows. I upgraded the system to a new version. The data from the old table was added to a new table of the new version nearly with the same structure.

    When I run a query on the new table to show the replies of a ticket on phpMyAdmin the results come quickly but through the PHP file, the page takes more than 120 sec to be loaded.

    The query I'm using:
    Code:
    SELECT * FROM tickets_replies WHERE ticket_id='20500' AND private_reply='0' ORDER BY id ASC LIMIT 0,30;
    the table structure :

    Code:
    CREATE TABLE tickets_replies (
      id int(11) NOT NULL auto_increment,
      ticket_id int(11) NOT NULL default '0',
      replyer varchar(255) NOT NULL,
      replye_rid int(11) NOT NULL,
      message longtext NOT NULL,
      messageread int(11) NOT NULL default '0',
      messageclientread int(11) NOT NULL,
      datetime int(20) default '0',
      replyer_ip longtext NOT NULL,
      private_reply int(11) NOT NULL,
      PRIMARY KEY  (id),
      KEY id (id),
      KEY ticket_id (ticket_id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 MAX_ROWS=40000000;
    I do not have any index in the old table.

    Any ideas about the causes of this problem ?

    your help would be much appreciated.
    Last edited by H i D D e N; Dec 26, 2008 at 06:15.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    PRIMARY KEY (id) and KEY id (id) are redundant, you do not need the extra KEY index

    KEY ticket_id (ticke_tid) has a typo, but it should be okay for the query you posted

    if the query works fine by itself but very slowly in php, then this has to be a php problem, yes?

    moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have you created index ?? if not then please add indexing for key

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    priti, which key were you referring to? he already has indexed the column, it's right there in post #1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937:

    thank you for moving the thread, I apologize for openining the thread in the wrong place but I thought the problem should be fixed through MySQL not php that's why I needed MySQL experience.

    the typo for (ticke_tid) was in the reply only, i've made a mistake while I was copying the table structure here.


    priti:

    I added the following keys :
    PRIMARY KEY (id),
    KEY id (id),
    KEY ticket_id (ticket_id)

    what other index to add, and for which field ?

    BTW, this is a part of the PHP code:
    Code:
    $RepliesCond = "WHERE ticket_id='$ticketinfo[id]' AND private_reply='0'"; 	
    $RepliesQuery = "SELECT * FROM tickets_replies $RepliesCond order by id ASC";
    
    $nums = $DB->db_num($DB->db_query($RepliesQuery));
    $getreplies = $DB->db_query($RepliesQuery." LIMIT 0,30");
    while($repliesinfo = $DB->db_fetch($getreplies))
    {			
    	eval("\$replies_list .= \"".getTemplate('replies_list')."\";");
    	$update = $DB->q("UPDATE tickets_replies SET messageclientread ='1' WHERE id='$repliesinfo[id]'");
    }

    Thanx for your responses.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I assume other php pages which send querys to the same database server execute quickly, like in a second or two? I'm not seeing 120 seconds here.

    Efficiency improvements could be made, but I'm doubtful these are your 120 seconds. They're improvements regardless, though.

    I'm not sure what getTemplate() does, but I think this is vbulletin? If I remember right it querys the database. You call this function in the loop to get the same template over and over. Unless the function internally caches the template, this is very inneficient.

    You also have an update query in the loop. All together, your snippet of code uses up to 61 querys.

    String concatenation is also kinda slow, although this should be very minor.

    PHP Code:
    // store the template in a variable for reuse
    $template getTemplate('replies_list');

    //make a list of id's to use for a single update query
    $ids = array();

    // make a string buffer
    $string_buffer = array();

    while(
    $repliesinfo $DB->db_fetch($getreplies))
    {            
        eval(
    "\$string_buffer[] = \"".$template."\";");
        
    $ids[] = $repliesinfo['id'];
    }

    // issue a single update query for all the ids
    $update $DB->q("UPDATE tickets_replies SET messageclientread ='1' WHERE id IN (" join(','$ids) . ")");

    $replies_list .= join(''$string_buffer); 
    Is this whole block of code, potentially inside of another database loop?

    Do some timing of parts of your script. see the microtime() function to measure execution time of parts of you code. You will find the bottleneck within minutes.


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
  •