Has anyone figured out a good way to avoid using
where myfield like '%something%'
to see if the word 'something' appears in myfield? I tried full text
index, but that doesn't work so well. Is there anything else to try?
Thanks!
-Corey
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Can you search on some other field which has an index instead of searching
on 'myfield'?
If you can't, and MUST search on 'myfield', you could try using regular
expressions.
I don't know exactly what your use cases are, but I have been using regular
expressions and they do the job pretty well.
Check the REGEXP command in the documentation
Good luck.
Paulo
-----Original Message-----
From: Corey Tisdale [mailto:corey@bbqguys.com]
Sent: Miercoles, 08 de Diciembre de 2004 10:05 a.m.
To: win32@lists.mysql.com
Subject: God awful 'like' slowness
Has anyone figured out a good way to avoid using
where myfield like '%something%'
to see if the word 'something' appears in myfield? I tried full text
index, but that doesn't work so well. Is there anything else to try?
Thanks!
-Corey
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=urcid@vw.com.mx
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Thanks for that; I should have been more clear. I tried match against,
but since so many of my important terms are 3 letters or less (brand
names that are abbreviations), I run into the problem that searches for
one brand show the most relevant matches from competing brands that
have a more than 3 letter company name (bad for vendor relationships)!
I was wondering if there was a 'contained in' type function that I
could build into an index or something
-Corey
On Dec 8, 2004, at 10:11 AM, Ignatius Reilly wrote:
> "where myfield like '%something%'"
> does not use your fulltext index!
> use the MATCH AGAINST construction instead
>
> HTH
> Ignatius
> _________________________
> ----- Original Message -----
> From: "Corey Tisdale"
> To:
> Sent: 08 December 2004 17:04
> Subject: God awful 'like' slowness
>
>
>> Has anyone figured out a good way to avoid using
>>
>> where myfield like '%something%'
>>
>> to see if the word 'something' appears in myfield? I tried full text
>> index, but that doesn't work so well. Is there anything else to try?
>>
>> Thanks!
>> -Corey
>>
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe:
> http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
>>
>>
>
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Cool. I'll give that a shot and see if it works. Thanks!
Corey
On Dec 8, 2004, at 10:23 AM, Urcid Pliego, Paulo wrote:
> Can you search on some other field which has an index instead of
> searching
> on 'myfield'?
>
> If you can't, and MUST search on 'myfield', you could try using regular
> expressions.
>
> I don't know exactly what your use cases are, but I have been using
> regular
> expressions and they do the job pretty well.
>
> Check the REGEXP command in the documentation
>
> Good luck.
>
> Paulo
>
> -----Original Message-----
> From: Corey Tisdale [mailto:corey@bbqguys.com]
> Sent: Miercoles, 08 de Diciembre de 2004 10:05 a.m.
> To: win32@lists.mysql.com
> Subject: God awful 'like' slowness
>
>
> Has anyone figured out a good way to avoid using
>
> where myfield like '%something%'
>
> to see if the word 'something' appears in myfield? I tried full text
> index, but that doesn't work so well. Is there anything else to try?
>
> Thanks!
> -Corey
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=urcid@vw.com.mx
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=corey@bbqguys.com
>
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Regexp still does not make use of the index, so I am in the same
position. Thanks for the tip though!
Corey
On Dec 8, 2004, at 10:36 AM, Corey Tisdale wrote:
> Cool. I'll give that a shot and see if it works. Thanks!
>
> Corey
>
> On Dec 8, 2004, at 10:23 AM, Urcid Pliego, Paulo wrote:
>
>> Can you search on some other field which has an index instead of
>> searching
>> on 'myfield'?
>>
>> If you can't, and MUST search on 'myfield', you could try using
>> regular
>> expressions.
>>
>> I don't know exactly what your use cases are, but I have been using
>> regular
>> expressions and they do the job pretty well.
>>
>> Check the REGEXP command in the documentation
>>
>> Good luck.
>>
>> Paulo
>>
>> -----Original Message-----
>> From: Corey Tisdale [mailto:corey@bbqguys.com]
>> Sent: Miercoles, 08 de Diciembre de 2004 10:05 a.m.
>> To: win32@lists.mysql.com
>> Subject: God awful 'like' slowness
>>
>>
>> Has anyone figured out a good way to avoid using
>>
>> where myfield like '%something%'
>>
>> to see if the word 'something' appears in myfield? I tried full text
>> index, but that doesn't work so well. Is there anything else to try?
>>
>> Thanks!
>> -Corey
>>
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe: http://lists.mysql.com/win32?unsub=urcid@vw.com.mx
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe:
>> http://lists.mysql.com/win32?unsub=corey@bbqguys.com
>>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=corey@bbqguys.com
>
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
No there is not but this what I usually do:
- create an additional column containing "index-searchable content"
how I prepare it:
- replace non-alphanum characters by "_"
- right-pad all words to at least 4 length with "_"
I will transform like wise the string to search
for ex. "I.Q." will become "I_Q_", "BMW" will become "BMW_"
so you will end up searching "I_Q_" against an index containing the indexed
word "I_Q_", which works well.
I find it unnecessary to reduce the full text min length parameter down from
4.
HTH
Ignatius
_________________________
----- Original Message -----
From: "Corey Tisdale"
To:
Sent: 08 December 2004 17:32
Subject: Re: God awful 'like' slowness
> Thanks for that; I should have been more clear. I tried match against,
> but since so many of my important terms are 3 letters or less (brand
> names that are abbreviations), I run into the problem that searches for
> one brand show the most relevant matches from competing brands that
> have a more than 3 letter company name (bad for vendor relationships)!
> I was wondering if there was a 'contained in' type function that I
> could build into an index or something
>
> -Corey
>
> On Dec 8, 2004, at 10:11 AM, Ignatius Reilly wrote:
>
> > "where myfield like '%something%'"
> > does not use your fulltext index!
> > use the MATCH AGAINST construction instead
> >
> > HTH
> > Ignatius
> > _________________________
> > ----- Original Message -----
> > From: "Corey Tisdale"
> > To:
> > Sent: 08 December 2004 17:04
> > Subject: God awful 'like' slowness
> >
> >
> >> Has anyone figured out a good way to avoid using
> >>
> >> where myfield like '%something%'
> >>
> >> to see if the word 'something' appears in myfield? I tried full text
> >> index, but that doesn't work so well. Is there anything else to try?
> >>
> >> Thanks!
> >> -Corey
> >>
> >>
> >> --
> >> MySQL Windows Mailing List
> >> For list archives: http://lists.mysql.com/win32
> >> To unsubscribe:
> > http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
> >>
> >>
> >
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
>
>
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Initially this seems like a pretty good solution. I'll ponder it a bit
to see how I can fit it to my problem, but I like it. Thanks for the
help!
-Corey
On Dec 8, 2004, at 10:49 AM, Ignatius Reilly wrote:
> No there is not but this what I usually do:
> - create an additional column containing "index-searchable content"
> how I prepare it:
> - replace non-alphanum characters by "_"
> - right-pad all words to at least 4 length with "_"
>
> I will transform like wise the string to search
> for ex. "I.Q." will become "I_Q_", "BMW" will become "BMW_"
>
> so you will end up searching "I_Q_" against an index containing the
> indexed
> word "I_Q_", which works well.
>
> I find it unnecessary to reduce the full text min length parameter
> down from
> 4.
>
> HTH
> Ignatius
> _________________________
> ----- Original Message -----
> From: "Corey Tisdale"
> To:
> Sent: 08 December 2004 17:32
> Subject: Re: God awful 'like' slowness
>
>
>> Thanks for that; I should have been more clear. I tried match against,
>> but since so many of my important terms are 3 letters or less (brand
>> names that are abbreviations), I run into the problem that searches
>> for
>> one brand show the most relevant matches from competing brands that
>> have a more than 3 letter company name (bad for vendor relationships)!
>> I was wondering if there was a 'contained in' type function that I
>> could build into an index or something
>>
>> -Corey
>>
>> On Dec 8, 2004, at 10:11 AM, Ignatius Reilly wrote:
>>
>>> "where myfield like '%something%'"
>>> does not use your fulltext index!
>>> use the MATCH AGAINST construction instead
>>>
>>> HTH
>>> Ignatius
>>> _________________________
>>> ----- Original Message -----
>>> From: "Corey Tisdale"
>>> To:
>>> Sent: 08 December 2004 17:04
>>> Subject: God awful 'like' slowness
>>>
>>>
>>>> Has anyone figured out a good way to avoid using
>>>>
>>>> where myfield like '%something%'
>>>>
>>>> to see if the word 'something' appears in myfield? I tried full text
>>>> index, but that doesn't work so well. Is there anything else to try?
>>>>
>>>> Thanks!
>>>> -Corey
>>>>
>>>>
>>>> --
>>>> MySQL Windows Mailing List
>>>> For list archives: http://lists.mysql.com/win32
>>>> To unsubscribe:
>>> http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
>>>>
>>>>
>>>
>>
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe:
> http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
>>
>>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=corey@bbqguys.com
>
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
I don't know if that is what you're looking for, but when I search
without quotes, it gives me the result you're searching for.
SELETC * FROM table WHERE column=VALUE;
Maybe that can help.
On Wed, 8 Dec 2004 11:02:30 -0600, Corey Tisdale
> Initially this seems like a pretty good solution. I'll ponder it a bit
> to see how I can fit it to my problem, but I like it. Thanks for the
> help!
>
>
>
> -Corey
>
> On Dec 8, 2004, at 10:49 AM, Ignatius Reilly wrote:
>
> > No there is not but this what I usually do:
> > - create an additional column containing "index-searchable content"
> > how I prepare it:
> > - replace non-alphanum characters by "_"
> > - right-pad all words to at least 4 length with "_"
> >
> > I will transform like wise the string to search
> > for ex. "I.Q." will become "I_Q_", "BMW" will become "BMW_"
> >
> > so you will end up searching "I_Q_" against an index containing the
> > indexed
> > word "I_Q_", which works well.
> >
> > I find it unnecessary to reduce the full text min length parameter
> > down from
> > 4.
> >
> > HTH
> > Ignatius
> > _________________________
> > ----- Original Message -----
> > From: "Corey Tisdale"
> > To:
> > Sent: 08 December 2004 17:32
> > Subject: Re: God awful 'like' slowness
> >
> >
> >> Thanks for that; I should have been more clear. I tried match against,
> >> but since so many of my important terms are 3 letters or less (brand
> >> names that are abbreviations), I run into the problem that searches
> >> for
> >> one brand show the most relevant matches from competing brands that
> >> have a more than 3 letter company name (bad for vendor relationships)!
> >> I was wondering if there was a 'contained in' type function that I
> >> could build into an index or something
> >>
> >> -Corey
> >>
> >> On Dec 8, 2004, at 10:11 AM, Ignatius Reilly wrote:
> >>
> >>> "where myfield like '%something%'"
> >>> does not use your fulltext index!
> >>> use the MATCH AGAINST construction instead
> >>>
> >>> HTH
> >>> Ignatius
> >>> _________________________
> >>> ----- Original Message -----
> >>> From: "Corey Tisdale"
> >>> To:
> >>> Sent: 08 December 2004 17:04
> >>> Subject: God awful 'like' slowness
> >>>
> >>>
> >>>> Has anyone figured out a good way to avoid using
> >>>>
> >>>> where myfield like '%something%'
> >>>>
> >>>> to see if the word 'something' appears in myfield? I tried full text
> >>>> index, but that doesn't work so well. Is there anything else to try?
> >>>>
> >>>> Thanks!
> >>>> -Corey
> >>>>
> >>>>
> >>>> --
> >>>> MySQL Windows Mailing List
> >>>> For list archives: http://lists.mysql.com/win32
> >>>> To unsubscribe:
> >>> http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
> >>>>
> >>>>
> >>>
> >>
> >>
> >> --
> >> MySQL Windows Mailing List
> >> For list archives: http://lists.mysql.com/win32
> >> To unsubscribe:
> > http://lists.mysql.com/win32?unsub=ignatius.reilly@free.fr
> >>
> >>
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=corey@bbqguys.com
>
>
> >
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=danieldaveiga@gmail.com
>
>
--
Daniel da Veiga
Computer Operator - RS - Brazil
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
--------------020801020801060308030207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I too am not sure what the desired results are, but I know that you can
search a list like this:
SELECT * FROM table WHERE column IN(comma separated list).
It also seems that there is a problem that some vendors have similar
abbreviations and a query is returning results from a vendor that is not
requested. I would suggest that you have a vendor table where each
vendor gets their own ID number, then store that vendor ID in the
original table. When doing a query it would be more like this:
SELECT * FROM table WHERE vendor_id = desired id
Just my two cents worth.
*Brandon Schenz*
Midwest Sports Supply
Phone: 513-956-4900
Fax: 513-956-4910
E-mail: brandons@midwestsports.com
Daniel da Veiga wrote:
>I don't know if that is what you're looking for, but when I search
>without quotes, it gives me the result you're searching for.
>
>SELETC * FROM table WHERE column=VALUE;
>
>Maybe that can help.
>
>
>On Wed, 8 Dec 2004 11:02:30 -0600, Corey Tisdale
>
>
>>Initially this seems like a pretty good solution. I'll ponder it a bit
>>to see how I can fit it to my problem, but I like it. Thanks for the
>>help!
>>
>>
>>
>>-Corey
>>
>>On Dec 8, 2004, at 10:49 AM, Ignatius Reilly wrote:
>>
>>
>>
>>>No there is not but this what I usually do:
>>>- create an additional column containing "index-searchable content"
>>>how I prepare it:
>>>- replace non-alphanum characters by "_"
>>>- right-pad all words to at least 4 length with "_"
>>>
>>>I will transform like wise the string to search
>>>for ex. "I.Q." will become "I_Q_", "BMW" will become "BMW_"
>>>
>>>so you will end up searching "I_Q_" against an index containing the
>>>indexed
>>>word "I_Q_", which works well.
>>>
>>>I find it unnecessary to reduce the full text min length parameter
>>>down from
>>>4.
>>>
>>>HTH
>>>Ignatius
>>>_________________________
>>>----- Original Message -----
>>>From: "Corey Tisdale"
>>>To:
>>>Sent: 08 December 2004 17:32
>>>Subject: Re: God awful 'like' slowness
>>>
>>>
>>>
>>>
>>>>Thanks for that; I should have been more clear. I tried match against,
>>>>but since so many of my important terms are 3 letters or less (brand
>>>>names that are abbreviations), I run into the problem that searches
>>>>for
>>>>one brand show the most relevant matches from competing brands that
>>>>have a more than 3 letter company name (bad for vendor relationships)!
>>>>I was wondering if there was a 'contained in' type function that I
>>>>could build into an index or something
>>>>
>>>>-Corey
>>>>
>>>>On Dec 8, 2004, at 10:11 AM, Ignatius Reilly wrote:
>>>>
>>>>
>>>>
>>>>>"where myfield like '%something%'"
>>>>>does not use your fulltext index!
>>>>>use the MATCH AGAINST construction instead
>>>>>
>>>>>HTH
>>>>>Ignatius
>>>>>_________________________
>>>>>----- Original Message -----
>>>>>From: "Corey Tisdale"
>>>>>To:
>>>>>Sent: 08 December 2004 17:04
>>>>>Subject: God awful 'like' slowness
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Has anyone figured out a good way to avoid using
>>>>>>
>>>>>>where myfield like '%something%'
>>>>>>
>>>>>>to see if the word 'something' appears in myfield? I tried full text
>>>>>>index, but that doesn't work so well. Is there anything else to try?
>>>>>>
>>>>>>Thanks!
>>>>>>-Corey
>>>>>>
>>>>>>
>>>>>>--
>>>>>>MySQL Windows Mailing List
>>>>>>For list archives: http://lists.mysql.com/win32
>>>>>>To unsubscribe:
>>>>>>
>>>>>>
>>>>>http://lists.mysql.com/win32?unsub=ignatius.reilly@free .fr
>>>>>
>>>>>
>>>>>>
>>>>>>
>>>>--
>>>>MySQL Windows Mailing List
>>>>For list archives: http://lists.mysql.com/win32
>>>>To unsubscribe:
>>>>
>>>>
>>>http://lists.mysql.com/win32?unsub=ignatius.reilly@free.f r
>>>
>>>
>>>>
>>>>
>>>--
>>>MySQL Windows Mailing List
>>>For list archives: http://lists.mysql.com/win32
>>>To unsubscribe: http://lists.mysql.com/win32?unsub=corey@bbqguys.com
>>>
>>>
>>
>>
>>--
>>MySQL Windows Mailing List
>>For list archives: http://lists.mysql.com/win32
>>To unsubscribe: http://lists.mysql.com/win32?unsub=danieldaveiga@gmail.com
>>
>>
>>
>>
>
>
>
>
--------------020801020801060308030207--