FULLTEXT on multiple related tables: Second opinion needed
Hi,
I’m trying to add fulltext search to multiple related tables. I think I came up with a good way to do this but since I’m new to MySQL I would really appreciate a second opinion. Here are the tables: I have a table named book and a table named author and a lookup table named book_author to join the two since each book can have multiple authors and each author could be associated with several books. I would like people to be able to search by keywords from the book table or by author’s name. After a lot of testing this is the query that I’ve come up with:
SELECT results.book_id, results.title, results.isbn, SUM(score) AS score
FROM (
SELECT book.book_id, book.title, book.isbn,
MATCH(book.title, book.subtitle) AGAINST(‘search words’) AS score
FROM book
WHERE book.display_status = 1
AND MATCH(book.title, book.subtitle) AGAINST(‘search words’)
UNION ALL
SELECT book.book_id, book.title, book.isbn,
MATCH(author.first_name, author.last_name) AGAINST(‘search words’) AS score
FROM book
INNER JOIN book_author ON book_author.book_id = book.book_id
INNER JOIN author ON author.author_id = book_author.author_id
WHERE book.display_status = 1
AND MATCH(author.first_name, author.last_name) AGAINST(‘search words’)
) AS results
GROUP BY results.book_id
ORDER BY score DESC
Queries are taking around .0075 seconds. This seems fine but I only have 11 rows in my book table, 6 rows in my author table and 12 rows in my book_author table. When I run the query with explain I get something similar to this(depending on the words used for search):
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort
| 2 | DERIVED | book | fulltext | ft_book | ft_book | 0 | | 1 | Using where
| 3 | UNION | author | fulltext | PRIMARY,ft_name | ft_name | 0 | | 1 | Using where
| 3 | UNION | book_author | ref | PRIMARY,author_id | author_id | 4 | publications.author.author_id | 1 | Using index
| 3 | UNION | book | eq_ref | PRIMARY | PRIMARY | 4 | publications.book_author.book_id | 1 | Using where
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL |
I’m worried that once the tables are full this will become slow. I can see from using EXPLAIN that my indexes and keys are being used. The only thing concerning me is the derived table which varies depending on the words searched for. Will this become a problem since its using temporary & filesort? Is this an affective way to perform this type of search?
The other option that I’m considering is creating a new table that holds all my searchable words for every book and would have a column for the book keywords and a column to hold all the authors. This would require whenever a change is made to the book or authors the appropriate rows get rebuilt. This seams much more involved and I would like to avoid this if possible but I think, in theory, it would perform much better.
I’ve struggled to find any information in books or online on how to do this. Any suggestions or direction would be appreciated.