<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Payam Poursaied wrote:
<blockquote cite="mid:098b01cac52a$1ec8e0e0$5c5aa2a0$@net" type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Tahoma","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Tahoma","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="Section1">
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">Hi
all<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">I’m
looking for a way to search and find those tickets which their subject
starts
with ‘FAX’.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">When
I use “subject matches ‘fax’” in advanced search, the search query is
something
like <o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">….<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">main.Subject
LIKE '%FAX%'<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">…<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">But
I’m looking for a way to have a query with higher performance like<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">….<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">main.Subject
LIKE 'FAX%'<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">…<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Tahoma","sans-serif";">Is
there any way to make this query? <o:p></o:p></span></p>
</div>
</blockquote>
Yes, there is. I did the same thing because it kills performance each
and everytime someone searches for something. Only thing is you need to
make your users aware of the fact that including % is now needed if
they don't know the exact search term.<br>
I think you'll find the pre/post pending of % in SearchBuilder but I'm
not completely sure so let me check.....<br>
Yes, its in SearchBuilder.pm the following is part of a patch that I
applied to our installation. Its here for reference only. Don't think
you can paste it to a file and apply it.<br>
---<br>
--- SearchBuilder.pm.orig Fri Jul 29 16:49:52 2005<br>
+++ SearchBuilder.pm Mon Aug 22 13:40:38 2005<br>
@@ -688,9 +688,15 @@<br>
<br>
if ( $args{'FIELD'} ) {<br>
<br>
- #If it's a like, we supply the %s around the search term<br>
+ #If it's a like, we supply the %s around the search term only
if its not Oracle<br>
+ #because for Oracle we'll use where
contains(content,'text')>1<br>
if ( $args{'OPERATOR'} =~ /LIKE/i ) {<br>
- $args{'VALUE'} = "%" . $args{'VALUE'} . "%";<br>
+ if ( $RT::DatabaseType == 'Oracle') {<br>
+ $args{'VALUE'} = $args{'VALUE'} <br>
+ }<br>
+ else {<br>
+ $args{'VALUE'} = "%" . $args{'VALUE'} . "%";<br>
+ }<br>
}<br>
elsif ( $args{'OPERATOR'} =~ /STARTSWITH/i ) {<br>
$args{'VALUE'} = $args{'VALUE'} . "%";<br>
@@ -864,6 +870,20 @@<br>
}<br>
<br>
Regards,<br>
<br>
Joop<br>
<br>
</body>
</html>