I'm using this query to perform a full text search on a MySQL database:\[code\]SELECT DISTINCT questions.id, questions.uniquecode, questions.spam,questions.questiondate,questions.userid,questions.description,users.login AS username,questions.questiontext,questions.totalvotes,MATCH(questions.questiontext, questions.uniquecode) AGAINST ('rock guitarist chick*' IN BOOLEAN MODE) AS relevance FROM questions LEFT JOIN users ON questions.userid = users.id LEFT JOIN answer_mapping ON questions.id = answer_mapping.questionid LEFT JOIN answers ON answer_mapping.answerid = answers.idLEFT JOIN tagmapping ON questions.id = tagmapping.questionidLEFT JOIN tags ON tagmapping.tagid = tags.id WHERE questions.spam < 10 AND ( MATCH(questions.questiontext, questions.uniquecode) AGAINST ('rock guitarist chick*' IN BOOLEAN MODE) OR MATCH(answers.answertext) AGAINST ('rock guitarist chick*' IN BOOLEAN MODE) OR MATCH (tags.tag) AGAINST ('rock guitarist chick*' IN BOOLEAN MODE)) GROUP BY questions.id ORDER BY relevance DESC\[/code\]The results are very relevant, but the search is really slow and is getting slower and slower as the tables grow.Table stats: questions - 400 records indexes
- PRIMARY BTREE - id
- BTREE - uniquecode
- BTREE - questiondate
- BTREE - userid
- FULLTEXT - questiontext
- FULLTEXT - uniquecode
- PRIMARY - BTREE - id
- BTREE - answerdate
- BTREE - questionid
- FULLTEXT - answertext
- PRIMARY - BTREE - id
- BTREE - answerid
- BTREE - questionid
- BTREE - userid
- PRIMARY - BTREE - id
- BTREE - tag
- FULLTEXT - tag
- PRIMARY - BTREE - id
- BTREE - tagid
- BTREE - questionid