DanBaker
New Member
Karma: +0/-0
Offline
: 1
|
 |
« : December 02, 2011, 02:02:39 AM » |
|
Thought that someone might find this helpful. Using a temporary table, I've enabled boolean searches, sorting by accuracy, quoted strings, etc. My users are very happy with these improvements. Here's the query:
DROP TEMPORARY TABLE IF EXISTS temp_table; CREATE TEMPORARY TABLE temp_table (ID INT(6), status ENUM('new','onhold','custreplied','awaitingcustomer','reopened','closed','raised'), timestamp TIMESTAMP, cat INT(5), subject VARCHAR(255), rep INT(5), email VARCHAR(255), priority TINYINT(1), name VARCHAR(255), fullTextString LONGTEXT); ALTER TABLE temp_table ADD FULLTEXT INDEX (fullTextString); ALTER TABLE temp_table DISABLE KEYS; INSERT INTO temp_table SELECT DISTINCT tickets. ID AS ID, tickets. status, tickets. timestamp as timestamp, tickets. cat AS cat, tickets. subject AS subject, tickets. rep AS rep, tickets. email AS email, priority, tickets. name AS name, CONCAT_WS(' ', tickets. email, tickets. name, tickets. subject, ticket_messages. message, ticket_answers. message, tickets. subject, tickets. name) as fullTextString FROM ( ( tickets LEFT JOIN ticket_messages ON ticket_messages. ticket = tickets. ID ) LEFT JOIN ticket_answers ON ticket_answers. reference = ticket_messages. ID ); ALTER TABLE temp_table ENABLE KEYS; SELECT DISTINT ID, MATCH(temp_table. fullTextString) AGAINST ('dan baker') AS Relevance, status, timestamp, cat, subject, rep, email, priority, name FROM temp_table WHERE MATCH(temp_table. fullTextString) AGAINST ('+dan +baker' IN BOOLEAN MODE) HAVING Relevance > 0. 2 ORDER BY Relevance DESC;
Searches for "Dan Baker" (unquoted). Let me know, if someone is interested I will post the PHP modification that need to be made to main. php to implement this query.
Dan
|