Re: Large Like Queries

[Brent Baisley]

Have you thought about creating a fulltext index? Then you can search on 
any word in a field. MySQL 4 has a lot more options for fulltext search, 
but you can still do it in 3.

On Monday, December 16, 2002, at 04:25 PM, Chris Stark wrote:

SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50;

I know that in a query such as this, mysql does not utilize indexing, 
so I
was just curious how some others have attempted to speed up this kind of
thing up

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Large Like Queries

[Stefan Hinz, iConnect \(Berlin\)]

Chris,

> SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50;
> I know that in a query such as this, mysql does not utilize indexing, so I
> was just curious how some others have attempted to speed up this kind of
> thing up

Maybe this answer won't help too much, but I experienced that MySQL _will_
use indexes, even if a LIKE comparison has a match pattern like "%...%"
(actually, the discussion is about the leading "%...").

In a 1.2 million records table, I found that a search like that took about
15 seconds without an index, and 2.4 seconds after we created an index on
the column in question.

I called this effect "pre-sorted comparison", but maybe someone else will
have a better explanation.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


- Original Message -
From: "Chris Stark" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, December 16, 2002 10:25 PM
Subject: Large Like Queries


> Hi,
>
> I was just wondering if anyone has any tricks for speeding up huge queries
> that require a LIKE comparison, and also a wild card at the front and back
> of the search term.  For example:
>
> SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50;
>
> I know that in a query such as this, mysql does not utilize indexing, so I
> was just curious how some others have attempted to speed up this kind of
> thing up
>
> Thanks,
> Chris
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Large Like Queries

[Chris Stark]

Hi,

I was just wondering if anyone has any tricks for speeding up huge queries
that require a LIKE comparison, and also a wild card at the front and back
of the search term.  For example:

SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50;

I know that in a query such as this, mysql does not utilize indexing, so I
was just curious how some others have attempted to speed up this kind of
thing up

Thanks,
Chris

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php