SitePoint Sponsor

User Tag List

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

    Slow SQL response

    I noticed recently one of my scripts has been giving a really slow response. It's taking ~5 seconds, sometimes more, it should take .5

    SELECT *, b.`name` as `class_name`, c.`name` as `subclass_name` FROM `db_items` a LEFT JOIN `db_item_classes` b ON a.`class_id` = b.`class_id` AND b.`lang` = 'en' LEFT JOIN `db_item_subclasses` c ON a.`class_id` = c.`class_id` AND a.`subclass_id` = c.`subclass_id` AND c.`lang` = 'en' WHERE c.`class_id` = b.`class_id` AND (a.`name` = 'a' OR a.`name` LIKE '%a%') AND a.`lang` = 'Array' GROUP BY a.`item_id` ORDER BY a.`name` LIMIT 1

    Any ideas?

  2. #2
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT *, a.`name` as `name`, b.`name` as `class_name`, c.`name` as `subclass_name` FROM `db_items` a LEFT JOIN `db_item_classes` b ON a.`class_id` = b.`class_id` LEFT JOIN `db_item_subclasses` c ON c.`class_id` = b.`class_id` AND a.`class_id` = c.`class_id` AND a.`subclass_id` = c.`subclass_id` WHERE (a.`name` = '".mysql_escape_string($_GET['query'])."' OR a.`name` LIKE '$name') AND a.`lang` = '$langid' AND b.`lang` = '$langid' AND c.`lang` = '$langid' GROUP BY a.`item_id` ORDER BY a.`name` LIMIT 1

    Updated query, ignore the PHP scripting (those parts are correct)

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    have you tried using EXPLAIN on that query in phpymyadmin or mysql cli?

  4. #4
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure how to read what explain tells me though, I didn't see anything helpful

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    paste the results of EXPLAIN here, along with the CREATE TABLE statements for db_items, db_item_classes, db_item_subclasses


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
  •