Actually, I'm not convinced it would, because I want to be able to
find a string that is *NOT fully contained* within any given field.
In other words, I want a search for
The catcher in%
to match the row:
+---------+----------------------------------------------------------+
| article | title |
+---------+----------------------------------------------------------+
| The | catcher in the rye |
CONCAT allows me to do this, but I'm hoping to optimize that operation
somehow, perhaps with an index.
Andy
olinux wrote:
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
