[thelist] Fun with MySQL

[Anthony Baratta]

-----Original message-----
From: Hershel Robinson hershel at galleryrobinson.com
Date: Wed, 05 Jul 2006 11:31:32 -0700
To: Thelist thelist at lists.evolt.org
Subject: [thelist] Fun with MySQL

> This is a non-trivial SQL question in a long-ish letter. The question is 
> going to be:
> 
> Does anyone know how I can do full-text searching on a temporary table? 
> Or perhaps an alternative solution to this problem?

I think you just need to tweak the way you are doing things, this post at mySQL should help.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Posted by Andrew Panin on October 7 2004 3:05pm	

Hi all! I had a problem with FULLTEXT search and after I solved it, I want to try to help you. I thought that FULLTEXT search is slow. That was. But I did a simple trick:

1. For example, we have 4 tables with fulltext index. We need to perform fast search on them.

2. Do the following:
CREATE TEMPORARY TABLE xxx SELECT id, name, MATCH(name) AGAINST ('search_string') AS relevancy FROM table1;
INSERT INTO xxx SELECT id, name, MATCH(name) AGAINST ('search_string') AS relevancy FROM table2 ...

3. Then, when the temporary table is filled with the data, do the simple select from it:

SELECT id, name FROM xxx ORDER BY relevancy DESC

4. That's all.

I think, it is the optimal way to make a VERY FAST fulltext search from a number of tables. I hope, this will be helpful.