Re: How to have faster select?
Read the manual, the most frequent words are stopped in index.
Chai-Hup Chen wrote:
> Hi,
> Thanks a lot, the select is much faster now. But I find something
> weird, it return nothing when match against the string "the", which
> return 255568 entries when select "% the %", is it possible mysql
> does not indexed some words for some reason?
>
>
> select count(*) from Books where match Title against ('cisco');
> +--+
> | count(*) |
> +--+
> | 222 |
> +--+
> 1 row in set (1.76 sec)
>
>
>
>
> select count(*) from Books where Title like "% the %";
> +--+
> | count(*) |
> +--+
> | 255568 |
> +--+
> 1 row in set (1 min 23.17 sec)
>
> select count(*) from Books where match Title against ('the');
> +--+
> | count(*) |
> +--+
> |0 |
> +--+
> 1 row in set (0.00 sec)
>
>> SELECT count(*) FROM Books WHERE MATCH Title AGAINST 'cisco'
>>
>> We use a FULLTEXT index search in a couple of our table for business referral
>> searches, and it works like a charm!
>>
>> Hope this helps
>>
Hi,
Here is the fulltext index I add:
CREATE TABLE Books (
isbn char(10) NOT NULL default '',
Title char(255) NOT NULL default '',
FULLTEXT KEY full_title (Title)
) TYPE=MyISAM;
select count(*) from Books --> 2028971
select count(*) from Books where Title like "% cisco %"
+--+
| count(*) |
+--+
| 86 |
+--+
1 row in set (1 min 15.58 sec)
Look like the fulltext index is not functioning ... Any idea?
>>>
>> Mike(mickalo)Blezien
>>
>> Thunder Rain Internet Publishing
>> Providing Internet Solutions that work!
>> http://www.thunder-rain.com
>> Tel: 1(225) 686-2002
>> =
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> -
>> 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
>
>
--
--
Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300
[EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583
whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net:
print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<>
)]}\EsMsKsN0[lN*1lK[d2%Sa2/d0
Re: How to have faster select?
On Sat, 31 Mar 2001 12:39:59 -0800 (PST), Chai-Hup Chen <[EMAIL PROTECTED]> wrote:
I think if you take a look at the MySQL docs on FULLTEXT it'll explain it alot
better then I probably could.
http://www.mysql.com/doc/M/y/MySQL_full-text_search.html
>> Thanks a lot, the select is much faster now. But I find something
>>weird, it return nothing when match against the string "the", which
>>return 255568 entries when select "% the %", is it possible mysql
>>does not indexed some words for some reason?
>>
>>
>>select count(*) from Books where match Title against ('cisco');
>>+--+
>>| count(*) |
>>+--+
>>| 222 |
>>+--+
>>1 row in set (1.76 sec)
>>
>>
>>
>>
>>select count(*) from Books where Title like "% the %";
>>+--+
>>| count(*) |
>>+--+
>>| 255568 |
>>+--+
>>1 row in set (1 min 23.17 sec)
>>
>>select count(*) from Books where match Title against ('the');
>>+--+
>>| count(*) |
>>+--+
>>|0 |
>>+--+
>>1 row in set (0.00 sec)
>>
>>>
>>> SELECT count(*) FROM Books WHERE MATCH Title AGAINST 'cisco'
>>>
>>> We use a FULLTEXT index search in a couple of our table for business referral
>>> searches, and it works like a charm!
>>>
>>> Hope this helps
>>>
>>> >>Hi,
>>> >> Here is the fulltext index I add:
>>> >>
>>> >>CREATE TABLE Books (
>>> >> isbn char(10) NOT NULL default '',
>>> >> Title char(255) NOT NULL default '',
>>> >> FULLTEXT KEY full_title (Title)
>>> >>) TYPE=MyISAM;
>>> >>
>>> >> select count(*) from Books --> 2028971
>>> >> select count(*) from Books where Title like "% cisco %"
>>> >>+--+
>>> >>| count(*) |
>>> >>+--+
>>> >>| 86 |
>>> >>+--+
>>> >>1 row in set (1 min 15.58 sec)
>>> >>
>>> >> Look like the fulltext index is not functioning ... Any idea?
>>>
>>> Mike(mickalo)Blezien
>>>
>>> Thunder Rain Internet Publishing
>>> Providing Internet Solutions that work!
>>> http://www.thunder-rain.com
>>> Tel: 1(225) 686-2002
>>> =
Mike(mickalo)Blezien
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225) 686-2002
=
-
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: How to have faster select?
Hi,
Thanks a lot, the select is much faster now. But I find something
weird, it return nothing when match against the string "the", which
return 255568 entries when select "% the %", is it possible mysql
does not indexed some words for some reason?
select count(*) from Books where match Title against ('cisco');
+--+
| count(*) |
+--+
| 222 |
+--+
1 row in set (1.76 sec)
select count(*) from Books where Title like "% the %";
+--+
| count(*) |
+--+
| 255568 |
+--+
1 row in set (1 min 23.17 sec)
select count(*) from Books where match Title against ('the');
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)
>
> SELECT count(*) FROM Books WHERE MATCH Title AGAINST 'cisco'
>
> We use a FULLTEXT index search in a couple of our table for business referral
> searches, and it works like a charm!
>
> Hope this helps
>
> >>Hi,
> >> Here is the fulltext index I add:
> >>
> >>CREATE TABLE Books (
> >> isbn char(10) NOT NULL default '',
> >> Title char(255) NOT NULL default '',
> >> FULLTEXT KEY full_title (Title)
> >>) TYPE=MyISAM;
> >>
> >> select count(*) from Books --> 2028971
> >> select count(*) from Books where Title like "% cisco %"
> >>+--+
> >>| count(*) |
> >>+--+
> >>| 86 |
> >>+--+
> >>1 row in set (1 min 15.58 sec)
> >>
> >> Look like the fulltext index is not functioning ... Any idea?
>
> Mike(mickalo)Blezien
>
> Thunder Rain Internet Publishing
> Providing Internet Solutions that work!
> http://www.thunder-rain.com
> Tel: 1(225) 686-2002
> =
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -
> 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
Re: How to have faster select?
On Sat, 31 Mar 2001 11:46:54 -0800 (PST), Chai-Hup Chen <[EMAIL PROTECTED]> wrote: Try this: SELECT count(*) FROM Books WHERE MATCH Title AGAINST 'cisco' We use a FULLTEXT index search in a couple of our table for business referral searches, and it works like a charm! Hope this helps >>Hi, >> Here is the fulltext index I add: >> >>CREATE TABLE Books ( >> isbn char(10) NOT NULL default '', >> Title char(255) NOT NULL default '', >> FULLTEXT KEY full_title (Title) >>) TYPE=MyISAM; >> >> select count(*) from Books --> 2028971 >> select count(*) from Books where Title like "% cisco %" >>+--+ >>| count(*) | >>+--+ >>| 86 | >>+--+ >>1 row in set (1 min 15.58 sec) >> >> Look like the fulltext index is not functioning ... Any idea? Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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: How to have faster select?
Hi,
Here is the fulltext index I add:
CREATE TABLE Books (
isbn char(10) NOT NULL default '',
Title char(255) NOT NULL default '',
FULLTEXT KEY full_title (Title)
) TYPE=MyISAM;
select count(*) from Books --> 2028971
select count(*) from Books where Title like "% cisco %"
+--+
| count(*) |
+--+
| 86 |
+--+
1 row in set (1 min 15.58 sec)
Look like the fulltext index is not functioning ... Any idea?
BTW, my mysql is 3.23.36 under Linux kernel 2.2.18, CPU P3 866, RAM 1GB
Best Regards,
Hup
>
> Why don't use FULLTEXT index ? This select doesn't use normal index so
> you have a sort of linear search
>
> Chai-Hup Chen wrote:
>
> > Hi,
> > I find in our database, select "% string %" is very slow (>1
> > minute). The database size have only 2M rows, any idea how could I get
> > faster response? Thanks a lot.
> >
> > CREATE TABLE Books (
> > isbn char(10) NOT NULL default '',
> > Title char(255) NOT NULL default '',
> > KEY isbn (isbn),
> > KEY Title (Title)
> > }
> >
> > select count(*) from Books -> 2028971
> >
> > select count(*) from Books where Title like "% cisco %";
> > +--+
> > | count(*) |
> > +--+
> > | 86 |
> > +--+
> > 1 row in set (1 min 25.09 sec)
> >
> >
> >
> > -
> > 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
> >
> >
>
> --
> --
> Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300
> [EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583
> whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net:
>
> print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<>
> )]}\EsMsKsN0[lN*1lK[d2%Sa2/d0
>
>
>
>
> -
> 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
Re: How to have faster select?
Why don't use FULLTEXT index ? This select doesn't use normal index so
you have a sort of linear search
Chai-Hup Chen wrote:
> Hi,
> I find in our database, select "% string %" is very slow (>1
> minute). The database size have only 2M rows, any idea how could I get
> faster response? Thanks a lot.
>
> CREATE TABLE Books (
> isbn char(10) NOT NULL default '',
> Title char(255) NOT NULL default '',
> KEY isbn (isbn),
> KEY Title (Title)
> }
>
> select count(*) from Books -> 2028971
>
> select count(*) from Books where Title like "% cisco %";
> +--+
> | count(*) |
> +--+
> | 86 |
> +--+
> 1 row in set (1 min 25.09 sec)
>
>
>
> -
> 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
>
>
--
--
Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300
[EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583
whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net:
print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<>
)]}\EsMsKsN0[lN*1lK[d2%Sa2/d0http://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: How to have faster select?
> MySQL doesn't use a table index when doing a string search like %string%, > but it MAY use the index when doing something string%. Is there a way you > can change your search to do a "string starts with" instead of a "string > contains" ? This should help with MySQL using the index that you want it to > use. Also, if you only search on whole 'words' only, eg. 'cisco', 'pc', 'router', etc. You could of course make a second table where you have split up Title into words (one entry for each word). In that table you could search without using like (and therefore use indexes). -- Aigars - 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: How to have faster select?
MySQL doesn't use a table index when doing a string search like %string%, but it MAY use the index when doing something string%. Is there a way you can change your search to do a "string starts with" instead of a "string contains" ? This should help with MySQL using the index that you want it to use. HTH See: http://www.mysql.com/doc/M/y/MySQL_indexes.html This link has some good info that might be of some help :) HTH Sam Masiello Systems Analyst Chek.Com (716) 853-1362 x289 [EMAIL PROTECTED] -Original Message- From: Chai-Hup Chen [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 29, 2001 12:59 PM To: [EMAIL PROTECTED] Subject: How to have faster select? Hi, I find in our database, select "% string %" is very slow (>1 minute). The database size have only 2M rows, any idea how could I get faster response? Thanks a lot. CREATE TABLE Books ( isbn char(10) NOT NULL default '', Title char(255) NOT NULL default '', KEY isbn (isbn), KEY Title (Title) } select count(*) from Books -> 2028971 select count(*) from Books where Title like "% cisco %"; +--+ | count(*) | +--+ | 86 | +--+ 1 row in set (1 min 25.09 sec) - 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
How to have faster select?
Hi, I find in our database, select "% string %" is very slow (>1 minute). The database size have only 2M rows, any idea how could I get faster response? Thanks a lot. CREATE TABLE Books ( isbn char(10) NOT NULL default '', Title char(255) NOT NULL default '', KEY isbn (isbn), KEY Title (Title) } select count(*) from Books -> 2028971 select count(*) from Books where Title like "% cisco %"; +--+ | count(*) | +--+ | 86 | +--+ 1 row in set (1 min 25.09 sec) - 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
