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
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
People 1 1
Keyword 10 1
You can include additional where clauses to meet your requirements but this is actually the fundamental idea.