Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: very slow search, need your kind help

    Excuse me for my poor english. I would like to get kind help of the members of this community.
    I am newbie at SQL. I had a code which was created not by me. This is it.

    SELECT b.id bug_id, b.title, b.application, b.long_desc, b.estimated_time, b.elapsed_time, b.pct_complete, paks.pak_desc, ed.ed_desc, CONCAT(cat.cat_no,'-',cat.cat_desc) cat_desc, u.uname poster, assigned.uname assigned_to, owner.uname owner, b.posted, b.last_update, b.view_count, b.blocker, milestones.milestone_desc, IF(b.closed=1,0,1) open, stat.status_color, stat.status_desc, CONCAT(sev.sev_no,'-',sev.sev_desc) severity, CONCAT(prio.prio_no,'-',prio.prio_desc) priority, COUNT(filestable.id) files, MIN(IF(a.confirmed=1,builds.build_no,NULL)) confirmed, MIN(IF(a.fixed=1,builds.build_no,NULL)) fixed, b.is_duplicate FROM bugstable b INNER JOIN paks ON b.pak = paks.id INNER JOIN affectstable a ON a.bug_id = b.id INNER JOIN builds ON a.build_no = builds.id INNER JOIN editions ed ON builds.edition = ed.id INNER JOIN categories cat ON b.category = cat.id LEFT OUTER JOIN userstable u ON u.id = b.uname LEFT OUTER JOIN statustable stat ON stat.id = b.status LEFT OUTER JOIN severity sev ON sev.sev_no = b.severity LEFT OUTER JOIN priority prio ON prio.prio_id = b.prio_id LEFT OUTER JOIN milestones ON milestones.id = b.target_milestone LEFT OUTER JOIN userstable assigned ON assigned.id = b.assigned_to LEFT OUTER JOIN userstable owner ON owner.id = b.owner_uid LEFT OUTER JOIN filestable filestable ON filestable.bug_id = b.id WHERE (b.title LIKE '%dasha%' OR b.application LIKE '%dasha%' OR b.long_desc LIKE '%dasha%' OR b.repro_steps LIKE '%dasha%' OR b.expectation LIKE '%dasha%' OR b.notice LIKE '%dasha%' OR b.id IN (SELECT bug_id FROM commentstable WHERE comment LIKE '%dasha%') OR b.id IN (SELECT bug_id FROM commentstable WHERE title LIKE '%dasha%')) AND (b.title LIKE '%%' OR b.application LIKE '%%' OR b.long_desc LIKE '%%' OR b.repro_steps LIKE '%%' OR b.expectation LIKE '%%' OR b.notice LIKE '%%' GROUP BY b.id, b.title, b.application, paks.pak_desc, ed.ed_desc, cat.cat_desc, u.uname, assigned.uname, owner.uname, b.posted, b.last_update, b.view_count, b.blocker, milestones.milestone_desc, b.closed, stat.status_color, stat.status_desc, sev.sev_no,sev.sev_desc, prio.prio_no,prio.prio_desc,b.is_duplicate

    After
    b.notice LIKE '%$srch_str%')

    I added following (maybe you will laugh, but I spent many hours to create it ;-))
    OR b.id IN (SELECT bug_id FROM commentstable WHERE comment LIKE '%$srch_str%') OR b.id IN (SELECT bug_id FROM commentstable WHERE title LIKE '%$srch_str%'))

    So the code becomes

    SELECT b.id bug_id, b.title, b.application, b.long_desc, b.estimated_time, b.elapsed_time, b.pct_complete, paks.pak_desc, ed.ed_desc, CONCAT(cat.cat_no,'-',cat.cat_desc) cat_desc, u.uname poster, assigned.uname assigned_to, owner.uname owner, b.posted, b.last_update, b.view_count, b.blocker, milestones.milestone_desc, IF(b.closed=1,0,1) open, stat.status_color, stat.status_desc, CONCAT(sev.sev_no,'-',sev.sev_desc) severity, CONCAT(prio.prio_no,'-',prio.prio_desc) priority, COUNT(filestable.id) files, MIN(IF(a.confirmed=1,builds.build_no,NULL)) confirmed, MIN(IF(a.fixed=1,builds.build_no,NULL)) fixed, b.is_duplicate FROM bugstable b INNER JOIN paks ON b.pak = paks.id INNER JOIN affectstable a ON a.bug_id = b.id INNER JOIN builds ON a.build_no = builds.id INNER JOIN editions ed ON builds.edition = ed.id INNER JOIN categories cat ON b.category = cat.id LEFT OUTER JOIN userstable u ON u.id = b.uname LEFT OUTER JOIN statustable stat ON stat.id = b.status LEFT OUTER JOIN severity sev ON sev.sev_no = b.severity LEFT OUTER JOIN priority prio ON prio.prio_id = b.prio_id LEFT OUTER JOIN milestones ON milestones.id = b.target_milestone LEFT OUTER JOIN userstable assigned ON assigned.id = b.assigned_to LEFT OUTER JOIN userstable owner ON owner.id = b.owner_uid LEFT OUTER JOIN filestable filestable ON filestable.bug_id = b.id WHERE (b.title LIKE '%dasha%' OR b.application LIKE '%dasha%' OR b.long_desc LIKE '%dasha%' OR b.repro_steps LIKE '%dasha%' OR b.expectation LIKE '%dasha%' OR b.notice LIKE '%dasha%' OR b.id IN (SELECT bug_id FROM commentstable WHERE comment LIKE '%dasha%') OR b.id IN (SELECT bug_id FROM commentstable WHERE title LIKE '%dasha%')) AND (b.title LIKE '%%' OR b.application LIKE '%%' OR b.long_desc LIKE '%%' OR b.repro_steps LIKE '%%' OR b.expectation LIKE '%%' OR b.notice LIKE '%%' OR b.id IN (SELECT bug_id FROM commentstable WHERE comment LIKE '%%') OR b.id IN (SELECT bug_id FROM commentstable WHERE title LIKE '%%')) GROUP BY b.id, b.title, b.application, paks.pak_desc, ed.ed_desc, cat.cat_desc, u.uname, assigned.uname, owner.uname, b.posted, b.last_update, b.view_count, b.blocker, milestones.milestone_desc, b.closed, stat.status_color, stat.status_desc, sev.sev_no,sev.sev_desc, prio.prio_no,prio.prio_desc,b.is_duplicate

    This let me search not only posts but also comments but my search became very-very slow.
    Searching only posts are very quick, but after I changed the code I have to wait for few minutes before the result will be displayed.

    After that one good man told me
    After
    ...ON filestable.bug_id = b.id
    insert this
    LEFT
    JOIN commentstable c
    ON c.bug_id = b.id
    After
    OR b.notice LIKE '%dasha%'
    insert this
    OR c.comment LIKE '%dasha%'
    OR c.title LIKE '%dasha%'
    and get rid of the IN stuff.

    I tried but it is stiil very slow (I guess speed didn`t change).
    How this code can be improved? I don`t know why but it seems to me that one more step and it will be work fine. If you know please help.
    Any advices are very thanked.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by sqlbaka
    How this code can be improved?
    You can keep the formatting of your SQL by highlighting the code and pressing the # button. This will make it much easier to read. Just glancing at the code shows lots of issues:
    • You haven't told us what the tables are like, what their indexes are or how many rows each table has but your code appears to table scan everything. This will make it slow.
    • like "%%" : this does very little except waste the servers time (I suppose it checks for not null values but I'm guessing this isn't the intention). Why not only include tests if they actually test for something.
    • like "%dasha%" : this tests that a field contains the string dasha but it won't use the indexes so it will result in a table scan. You could use full text indexes but this could be done later.
    • in ( ... ) : this checks that a certain value is in a list of values. IN is slower than using the EXISTS clause. Both are pointless if you're pulling back every record anyway.
    • AND (b.title LIKE '%%' OR b.application LIKE '%%' OR b.long_desc LIKE '%%' OR b.repro_steps LIKE '%%' OR b.expectation LIKE '%%' OR b.notice LIKE '%%' : this part of the SQL doesn't have an ending bracket so it shouldn't run. It also tests for nothing at all.
    • OUTER JOIN : these aren't going to help either.
    Quote Originally Posted by sqlbaka
    Any advices are very thanked.
    I'd suggest you learn SQL, get rid of this code and then start from scratch.

    Mike
    Last edited by mike_bike_kite; 07-07-09 at 05:49.

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Quote Originally Posted by mike_bike_kite
    You can keep the formatting of your SQL by highlighting the code and pressing the # button. This will make it much easier to read. Just glancing at the code shows lots of issues:
    • You haven't told us what the tables are like, what their indexes are or how many rows each table has but your code appears to table scan everything. This will make it slow.
    • like "%%" : this does very little except waste the servers time (I suppose it checks for not null values but I'm guessing this isn't the intention). Why not only include tests if they actually test for something.
    • like "%dasha%" : this tests that a field contains the string dasha but it won't use the indexes so it will result in a table scan. You could use full text indexes but this could be done later.
    • in ( ... ) : this checks that a certain value is in a list of values. IN is slower than using the EXISTS clause. Both are pointless if you're pulling back every record anyway.
    • AND (b.title LIKE '%%' OR b.application LIKE '%%' OR b.long_desc LIKE '%%' OR b.repro_steps LIKE '%%' OR b.expectation LIKE '%%' OR b.notice LIKE '%%' : this part of the SQL doesn't have an ending bracket so it shouldn't run. It also tests for nothing at all.
    • OUTER JOIN : these aren't going to help either.

    I'd suggest you learn SQL, get rid of this code and then start from scratch.

    Mike
    Thanks for your reply and analys (I learned few things from it). I made mistake in my first post, but it doesn`t matter. You right, I should learn SQL, if I want to use it I would try. Thanks one more time.

Posting Permissions

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