very slow search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

very slow search

Author

Topic

sqlbaka
Starting Member

4 Posts

Posted - 2009-07-03 : 06:49:53

Excuse me for my poor english. I would like to get kind help of the members of this community.
I had a code which (of course) was created not by me. This is one part of code.
$where.= " NOT (b.title LIKE '%$srch_str%' OR";
$where.= " b.application LIKE '%$srch_str%' OR";
$where.= " b.long_desc LIKE '%$srch_str%' OR";
$where.= " b.repro_steps LIKE '%$srch_str%' OR";
$where.= " b.expectation LIKE '%$srch_str%' OR";
$where.= " b.notice LIKE '%$srch_str%')";

I changed this part
$where.= " b.notice LIKE '%$srch_str%')";

to below (maybe you will laugh, but I spent many hours before created it ;=))

if (isset($_POST['quicksearch']) && $_POST['quicksearch'] == "truly"):
$where.= " b.notice LIKE '%$srch_str%')";
else:
$where.= " b.notice LIKE '%$srch_str%' OR";
$where.= " b.id IN (SELECT bug_id FROM commentstable WHERE comment LIKE '%$srch_str%') OR";
$where.= " b.id IN (SELECT bug_id FROM commentstable WHERE title LIKE '%$srch_str%'))";
endif;

this let me search not only posts but also comments BUT my search became very-very long.
I have to wait for few minutes before the result will be displayed.
Is it because of my stupid sql code or there is some other reasons? How this code can be improved?
Any advices are very thanked.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 07:02:38

Looks like PHP.
Are you using SQL Server or MySQL?

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlbaka
Starting Member

4 Posts

Posted - 2009-07-03 : 07:07:37

Thank you.
You are right this is PHP.
I use MySQL and phpMyAdmin on Kubuntu.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 07:13:46

Oh but this is an MS SQL Server forum.
Maybe you can get more professional help about performance tuning in a forum for MYSQL?

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 07:54:57

Doesn't matter. The wildcard searches will always be slow since no index(es) can be used.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 08:19:44

If its sql server we can use the DMV functions to find out the Index usage....

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so we can decide whether they should be implemented.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 09:59:18

If a query uses wildcard searches, no index in the world will help you.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

sqlbaka
Starting Member

4 Posts

Posted - 2009-07-06 : 21:16:25

By the way, that is the all code before
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
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

Yesterday some good man tell me to do next
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 still the same. But it seems to be close to the answer (I don`t know why but it seems so to me)
If you have an idea on what`s wrong please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 21:20:39

as suggest by Webfred, you will be better help at a MySQL forum like dbforums.com or forums.mysql.com

KH
[spoiler]Time is always against us[/spoiler]

sqlbaka
Starting Member

4 Posts

Posted - 2009-07-09 : 05:10:24

Thank all of you for answers. I really should ask for help at a MySQL forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources