8

Any full-text, MySQL experts out there?

I wrote a question yesterday asking for any suggestions on how to use full-text search with a lot of joins, a bit lazily, I hadn't actually made an attempt.

Since then, I've a had a good attempt at mastering it myself, building a few successful test queries, against my full-text fields. If I do NOT use any joins, and query each field individually, the full-text functions work perfectly and relevance/boolean works great with great performance too... but... as soon as I add my joins to run the full query, it runs forever and ever.

Can anybody spot anything in my query that might cause this to happen, because as an amateur, I really can't see it!

SELECT

photos.photoID,
photos.headline,
photos.dateCreated,
MATCH (people.people) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Rel1

FROM photos

LEFT JOIN ( photoPeople INNER JOIN people ON photoPeople.peopleID = people.PeopleID ) 
ON photos.photoID = photoPeople.photoID AND MATCH (people.people) AGAINST ('+sarah +harding' IN BOOLEAN MODE)

WHERE

photos.photoStatus = 'Live'

GROUP BY

photos.photoID

ORDER BY Rel1

This is one of my successful, individual, queries:

SELECT

photoID,
headline,
dateCreated,
MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Relevance

FROM photos

WHERE

photoStatus = 'Live'

AND

MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE)

ORDER BY Relevance

This is the DB schema:

photos (tbl)
photoID             INT(11)         Primary     Auto-Increment
headline            Long-Text
caption             Long-Text / FULLTEXT
dateCreated         DateTime

people (tbl)
peopleID            INT(11)         Primary     Auto-Increment
people              VarChar(255) / FULLTEXT

photoPeople (tbl)
photoID             INT(11)
peopleID            INT(11)

keywords (tbl)
keywordID           INT(11)         Primary     Auto-Increment
keyword             VarChar(255) / FULLTEXT

photoKeyword (tbl)
photoID             INT(11)
keywordID           INT(11)

photoContributor (tbl)
photoID             INT(11)
contributorRef      VarChar(100) / FULLTEXT

And this is my EXPLAIN print out:

id  select_type         table               type        possible_keys       key     key_len     ref     rows
1   SIMPLE              photos              ALL         NULL                NULL        NULL        NULL        89830
1   SIMPLE              photoContributor    ALL         NULL                NULL        NULL        NULL        149635  
1   SIMPLE              photoPeople         ALL         NULL                NULL        NULL        NULL        110606
1   SIMPLE              people              eq_ref      PRIMARY             PRIMARY     4       1   
1   SIMPLE              photoKeyword        ALL         NULL                NULL        NULL        NULL        699102  
1   SIMPLE              keywords            eq_ref      PRIMARY             PRIMARY     4       1

My website visitor should be able to search for: "Brad Pitt Angelina Jolie Dorchester Hotel Sunglasses @MG" - This should find "Brad Pitt" and "Angelina Jolie" from the 'people.people' table and possibly the 'photos.caption' table too. It should also find "Dorchester Hotel" from the 'photos.caption' table, "Sunglasses" from the 'keywords.keyword' table and lastly, it should find "@MG" in 'photoContributor.contributorRef' table.

Any help on this would be gratefully received...

TheCarver
  • 19,391
  • 25
  • 99
  • 149
  • Add `EXPLAIN SELECT ...` to your question – sanmai Sep 08 '11 at 16:55
  • For reference, if you are expanding on a question, without actually changing the basic information asked for, then you should just edit the original, rather than starting afresh. – Orbling Sep 08 '11 at 16:56
  • You need to paste the schema for those tables (the result of `SHOW CREATE TABLE` queries should be fine). Also, to be clear, it seems you are using MyISAM's fulltext indexing? – TehShrike Sep 08 '11 at 17:07
  • Hi TehShrike. Yes, I am using MyISAM on my tables. I have also edited the question to include a basic DB schema... – TheCarver Sep 08 '11 at 17:20
  • 1
    @Martin The problem is that if the table uses fulltext index then this table is unable to use another index. This means that joining operation between tables is done without index. So I offer you to use only one (or perhaps several) fulltext searches in your query. Other searches I recommend to replace with `LIKE` clause. You should test which full text seach in your query works the best. – Karolis Sep 08 '11 at 17:33
  • Martin- Honestly I'd really consider using another fulltext search; I transfered over to Sphinx a while back and haven't looked back.. performance drastically improved, and it's just a far superior Fulltext search. It's extremely easy and integrates very nicely into MySQL. – Petrogad Sep 08 '11 at 17:52
  • Sphinx sounds good but before I can compare the two methods, I need to get the first method, at least working. If it's still slow, I then will consider another option. I know of developers that have 16million+ records, using MySQL's full-text and works perfectly... – TheCarver Sep 08 '11 at 18:21
  • @Karolis - I don't think this is the issue. I have tested each individual full-text query using EXPLAIN SELECT and all of my full-text fields are working fine. I have noticed as soon as I use my joins, the indexing is not working - it's going through all the rows in each table, yet without joins, it goes straight to the 1 row. – TheCarver Sep 08 '11 at 18:25
  • @Martin This is definitely an issue. All your tables in this query uses full text indexes. But those tables are unable to use other indexes (primary and foreign keys) for table joining operation. This is the main problem about full text indexes. – Karolis Sep 08 '11 at 20:57
  • @Karolis - I understand what you are saying now. So... because my "caption" field is the biggest field, that would normally use a %LIKE% clause and causes all the slow problems - I would leave that as the FULLTEXT field and have the others as normal indexes. The only problem with that, I see, is I cannot use the "%" prefix on my LIKE clauses, if I did, I lose the INDEX... I think I've officially gone past my mental ability with this :( – TheCarver Sep 08 '11 at 23:24

2 Answers2

6

I suggest using separate smaller queries and using UNION to create a combined result set.

Here is another question with a similar task. Mysql Full text search across more than one table

Update

SELECT "Photos" AS TableName, photoID AS ID, MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Relevance
FROM photos
UNION ALL
SELECT "People" AS TableName, peopleID AS ID, MATCH (people) AGAINST ('+sarah +harding' IN BOOLEAN MODE)
FROM people
UNION ALL 
SELECT "Keyword" AS TableName, keywordID AS ID, MATCH (keyword) AGAINST ('+sarah +harding' IN BOOLEAN MODE)
FROM keyword

I don't really know what data you have but it would produce something like:

Table Name | ID | Relevance

Photos | 1 | 1

Photos | 2 | 0

People | 1 | 1

Keyword | 10 | 1

You can add additional where clauses to suit your needs but this is the basic idea.

Community
  • 1
  • 1
Jared
  • 12,406
  • 1
  • 35
  • 39
  • 1
    But doesn't UNION join results with the same columns. All the tables I query have different columns... – TheCarver Sep 09 '11 at 00:14
  • Union requires that columns have the same data type not the same name. – Jared Sep 09 '11 at 13:04
  • Thanks for your response. I'm not sure how to implement this in the kind of search that I have – TheCarver Sep 09 '11 at 18:03
  • I added a litle update of an example I hope it helps to illustrate what I am talking about. – Jared Sep 09 '11 at 20:53
  • Thank-you Jrod, that is helpful, although I'm still not sure how to use this with my structure. From what I can make out, that query will produce a lot of photoID's, a lot of keywordID's and a lot of peopleID's, instead of all photoID's. – TheCarver Sep 09 '11 at 22:25
  • I think I have a solution. I read an article that said, because you *cannot* use fulltext with joins, it is a good idea to break normalization rules and build a de-normalized table containing all the search words, captions, keywords and peoples names, then run fulltext queries against that. It doesn't sound like a bad idea apart from inserting in to two tables when adding data to my site – TheCarver Sep 09 '11 at 22:29
0

I suggest not using MySQL and using a real FTS solution instead, such as Lucene, which is often combined with MySQL by those who want FTS in MySQL. https://stackoverflow.com/questions/553055/best-full-text-search-for-mysql

Community
  • 1
  • 1
Morg.
  • 697
  • 5
  • 7