query optimization with CONCAT query

[Andy Ingham]

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