Mysql 4.0.1+ supports searches IN BOOLEAN MODE So if mysql 4 is an option, I think that would work for you.
http://www.mysql.com/doc/en/Fulltext_Search.html olinux --- Andy Ingham <[EMAIL PROTECTED]> wrote: > Folks -- > > We have built a table with bibliographic information > that contains a > field for initial articles and for titles (sans > initial articles). > > Some sample data (with all other fields suppressed): > > +---------+----------------------------------------------------------+ > | article | title > | > +---------+----------------------------------------------------------+ > | The | '60s (mini) > | > | | À Nous la Liberté [AKA: Freedom for Us | > Liberty for Us] | > | | Full Speed [AKA: À toute vitesse] > | > | El | Ángel exterminador [AKA: The > Exterminating Angel] | > | The | Farm: Angola, USA [AKA: The Farm] > +---------+----------------------------------------------------------+ > > For searching, we want to check > > article (+) title > > So, we've used CONCAT, as shown below from an > example query from our > slow query log: > > SELECT FILM.length, FILM.film_id, FILM.article, > FILM.title, > FILM.release_date, FILM.description, > FILM.displayFlag FROM (FILM LEFT > JOIN FILM_DIRECTOR ON FILM.film_id = > FILM_DIRECTOR.film_id) LEFT JOIN > DIRECTOR ON FILM_DIRECTOR.director_id = > DIRECTOR.director_id WHERE > FILM.displayFlag = 'yes' AND (CONCAT(article,title) > LIKE '%paris is > burning%') ORDER BY title, release_date; > > I am under the impression (from testing and reading > previous posts) that > it is not possible to construct an index on the > CONCATENATION of two > fields. A multi-column index on the article and > title fields only helps > if the query is like: > > ... article LIKE '%something%' and title LIKE > '%something%' ... > > which doesn't help us. > > Likewise, a FULLTEXT index only finds a string that > is *fully contained* > within any of the fields defined in that index. > > Any ideas of how to address this issue? > > TIA for any insights, > Andy > > Andy Ingham > Systems Librarian > Academic Affairs Library > UNC-Chapel Hill > 919-962-1288 > [EMAIL PROTECTED] > > > > > --------------------------------------------------------------------- > 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 > __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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
