Need help with mysql search engine
Hi, I currently have a table with 2 text fields, both of which are marked as fulltext. I'm using the full-text boolean search method with fairly good results. My problems however are: 1. I am on a shared host provider so I can't tweak the full-text search options such as stop words or minimum query length. 2. I would like to be able to search for words as short as 2 characters, but I'm limited by the default mysql configuration to 4 characters. 3. I'd like to get around the stopwords/stop characters. A lot of people search for " 802.11g ", but that search always comes up empty because I think the . character is messing up my searches. 4. Since I'm using the full-text search with a small amount of data, I would like all the results to be returned, even if they keywords are in more or less than 50% of the rows. 5. I really like the +, -, and "" for searching in full-text boolean mode, so I'd like to preserve that functionality. Since I'm on a shared host provider and can't really optimize the mysql configuration, is there any suggestions to help me improve my search results? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL search engine
Have you tried "explain?" Have you indexed the table? Curtis On Sun, 8 Feb 2004, ___bug wrote: > Hello, > > > What is the best way to search a table with about 500.000 rows of varchar > type. > --- > / id / time / name / > --- > Like '%blah%' and x Like '%blub%' is too slow (takes about 20 sec) > I tried using a fulltext index and search by using MATCH() AGAINST() > The problem is each varchar (name) contains not space seperated words but > they are combined with . or - or _ > For example I have the following row: > > | 567456 | 20040102 | Owg-08299-abzu_via.lap-2003 | > > Now i want to be able to search for (sorted by date): > Owg abzu lap > or > 082 abzu_via 2003 > or > Owg-08299-abzu_via.lap-2003 > or > Owg 08299 abzu via lap 2003 > or > 99-abzu > > Anyone can help me? > > Thanks Chris > > > -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL search engine
Hello, What is the best way to search a table with about 500.000 rows of varchar type. --- / id / time / name / --- Like '%blah%' and x Like '%blub%' is too slow (takes about 20 sec) I tried using a fulltext index and search by using MATCH() AGAINST() The problem is each varchar (name) contains not space seperated words but they are combined with . or - or _ For example I have the following row: | 567456 | 20040102 | Owg-08299-abzu_via.lap-2003 | Now i want to be able to search for (sorted by date): Owg abzu lap or 082 abzu_via 2003 or Owg-08299-abzu_via.lap-2003 or Owg 08299 abzu via lap 2003 or 99-abzu Anyone can help me? Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL search engine
A while ago we chatted about a better MySQL search engine. So I wrote a script to turn the list into web pages, and indexed these and www.mysql.com using the alkaline search engine. the url is http://workinout.com:9998/workinout/search.html Over time I will add improvements a. produce a list of useful links, parsed from the emails b. produce a dynamic FAQ page c. summarize problems into a dynamic top ten list d. other useful sorts, filters and regex output, as i think of it. Comments welcome anytime. Thanks Jim Danforth - 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: PHP/MySQL Search Engine Query Question
This is more of a php question. explode $string on " " (space) which will give you an array. Loop through the array first with $sql.="WHERE whatevercolumn LIKE '%$val%'" then for every other entry change WHERE to AND. Serge. - Original Message - From: "Paul Maine" <[EMAIL PROTECTED]> To: "MySQL MySQL" <[EMAIL PROTECTED]> Sent: Saturday, July 27, 2002 10:32 PM Subject: PHP/MySQL Search Engine Query Question > I am currently working on a website that is implemented using PHP and MySQL. > > The site currently has a simple search engine that allows a shopper to type > in a search string that is stored in $search. For example, if a shopper > types in 1972 Ford Mustang > $string ="1972 Ford Mustang" > > Using the following SQL statement: > SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% > > Records are returned that have this exact string and in this exact order > (I'm aware a wild card character is included on the front and back of the > string). > > My desire is to be able to logically AND each token of the search together > independent or the order of the tokens. > I want to return all records that have Mustang AND 1972 AND Ford. > > Since a shopper inputs the search string in advance I don't know how many > tokens will be used. > > I would appreciate any suggestions. > > Regards, > Paul > > - > 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: PHP/MySQL Search Engine Query Question
Pada Sat, 27 Jul 2002 21:32:48 -0500
"Paul Maine" <[EMAIL PROTECTED]> menulis :
> $string ="1972 Ford Mustang"
>
> Using the following SQL statement:
> SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search%
> I want to return all records that have Mustang AND 1972 AND Ford.
my suggestion :
change your script into this one :
select * from whatevertable where $string;
$string="whatevercolumns like \"%1972%\" and whatevercolumns like \"%Ford%\" and
whatevercolumns like \"%1972%\" "
or you can try this :
$query="1972 Ford Mustang"
$query=ereg_replace(" ","%\" and whatevercolumns like \"%",$query);
$string="whatevercolumns like \"%$query%\"";
select * from whatevertable where $string;
--
Write clearly - don't be too clever.
- The Elements of Programming Style (Kernighan & Plaugher)
MySQL 3.23.51 : up 37 days, Queries : 353.647 per second (avg).
--
Dicky Wahyu Purnomo - System Administrator
PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790)
Phone : +62 21 79199577 - Web : http://1rstwap.com
-
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
PHP/MySQL Search Engine Query Question
I am currently working on a website that is implemented using PHP and MySQL. The site currently has a simple search engine that allows a shopper to type in a search string that is stored in $search. For example, if a shopper types in 1972 Ford Mustang $string ="1972 Ford Mustang" Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% Records are returned that have this exact string and in this exact order (I'm aware a wild card character is included on the front and back of the string). My desire is to be able to logically AND each token of the search together independent or the order of the tokens. I want to return all records that have Mustang AND 1972 AND Ford. Since a shopper inputs the search string in advance I don't know how many tokens will be used. I would appreciate any suggestions. Regards, Paul - 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
