Any full-text, MySQL experts available?

I authored an issue yesterday requesting any suggestions regarding how to use full-text search with many different joins, a little lazily, I had not really made an effort.

Since that time, I have a were built with a good attempt for learning it myself, creating a couple of effective test queries, against my full-text fields. If I don't use any joins, and query each area individually, the entire-text functions work perfectly and relevance/boolean is effective with great performance too... but... the moment I add my joins to operate the entire query, it runs forever and ever.

Can anybody place anything during my query that may cause this to occur, because when a novice, I truly can't view 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 just one of my effective, 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 actually 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

Which is my EXPLAIN print:


id  choose_type         table               type        possible_secrets       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              key phrases            eq_ref      PRIMARY             PRIMARY     4       1

This site customer should have the ability to look for: "Kaira Pitt Julia Roberts Dorchester Hotel Shades @MG" - This will find "Kaira Pitt" and "Julia RobertsInch in the 'people.people' table and perhaps the 'photos.caption' table too. It will also find "Dorchester Hotel" in the 'photos.caption' table, "Shades" in the 'keywords.keyword' table and finally, it will find "@MG" in 'photoContributor.contributorRef' table.

Any help on this is gratefully received...

I would recommend using separate more compact queries and taking advantage of UNION to produce a combined result set.

Here's another question having 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 do not fully realize what data you've however it would produce something similar to:

Table Title ID Relevance

Photos 1 1

Photos 2

People 1 1

Keyword 10 1

You can include additional where clauses to meet your requirements but this is actually the fundamental idea.