[thelist] Fun with MySQL
Anthony Baratta
anthony at baratta.com
Wed Jul 5 15:34:09 CDT 2006
-----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.
More information about the thelist
mailing list