[thelist] Fun with MySQL

[Hershel Robinson]

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?

So for those of you not interested, you may carry on with the rest of 
your lives now.

For those of you interested, I have a site with magazine indexes, 
meaning tables of contents for back issues. We are now adding radio show 
indexes, because the magazine now has a radio show. The search SQL for 
the indexes is in place and works well:

SELECT Count(*) FROM `indexes` WHERE MATCH (author,title,blurb) AGAINST 
('{$_REQUEST['searchText']}');"

The $_REQUEST part is PHP, but this is actually strictly a MySQL SQL 
question.

What I need to do now is to extend that search to search also in the 
radio indexes. The radio shows do not have as many fields as do the 
magazine indexes, but I don't think this is relevant. The SQL for these 
tables I put at the bottom of this letter in case you want to see that.

The basic approach that almost works is to create a temporary table and 
store the results there of a search for magazine indexes and then radio 
show indexes and then display the whole temporary table.  This code 
indeed works in testing:

DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp SELECT author, title, blurb FROM `indexes` 
WHERE MATCH (author,title,blurb) AGAINST ('Weiss');
INSERT tmp (author, title) SELECT id, author, title FROM `radio_details` 
WHERE MATCH (author,title) AGAINST ('Weiss');
SELECT * FROM tmp

The problem is that it's not sorted according to relevancy. As should be 
clear, the tmp table has first magazine indexes and then radio show 
indexes. This is not quite correct, because I want to take advantage of 
the relevancy feature of MySQL's full-text search function.

So I thought to add the WHERE MATCH (author,title) AGAINST ('Weiss') to 
the end of the SELECT * FROM tmp. In order to do this, however, there 
must be full-text indexing defined on the tmp table. So I added that and 
the following code runs, but it brings up no records:

==============================
DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (
   `ID` int(11) NOT NULL auto_increment,
   `magID` int(11) NOT NULL,
   `issue` int(11) NOT NULL,
   `title` varchar(100) ,
   `author` varchar(50) NOT NULL default '',
   `blurb` mediumtext NOT NULL,
   `pageNum` mediumint(9) NOT NULL default '0',
    UNIQUE KEY (`ID`),
  FULLTEXT KEY `fullSearch` (`title`,`author`,`blurb`)
);
INSERT tmp (ID, author, title, blurb, pageNum ) SELECT ID, author, 
title, blurb, pageNum FROM `indexes` WHERE MATCH (author,title,blurb) 
AGAINST ('Weiss');
INSERT tmp (ID, author, title) SELECT ID, author, title FROM 
`radio_details` WHERE MATCH (author,title) AGAINST ('Weiss');
SELECT * from tmp WHERE MATCH (author,title,blurb) AGAINST ('Weiss');
==============================

If I remove the the last WHERE MATCH (author,title,blurb) AGAINST 
('Weiss') clause, then the search works and the result set has records 
from both magazine indexes and radio show indexes. When I add it, then 
no records come up--appears as though either the full-text searching 
doesn't work or I am not doing it correctly.

So the question is, does anyone know how I can do full-text searching on 
a temporary table? Or perhaps an alternative solution to this problem?

Thanks,
Hershel

PS:

SQL for tables:

CREATE TABLE `radio_details` (
   `ID` int(11) NOT NULL auto_increment,
   `title` varchar(255) default NULL,
   `author` varchar(255) default NULL,
   `issue` int(11) default NULL,
   PRIMARY KEY  (`ID`),
   FULLTEXT KEY `title` (`title`,`author`)
) TYPE=MyISAM;

CREATE TABLE `indexes` (
   `ID` int(11) NOT NULL auto_increment,
   `magID` int(11) NOT NULL default '0',
   `issue` int(11) NOT NULL default '0',
   `title` varchar(100) NOT NULL default '',
   `author` varchar(50) NOT NULL default '',
   `blurb` mediumtext NOT NULL,
   `pageNum` mediumint(9) NOT NULL default '0',
   `column` tinyint(4) NOT NULL default '0',
   `supplement` tinyint(1) NOT NULL default '0',
   `saleID` mediumint(9) NOT NULL default '0',
   UNIQUE KEY `ID_2` (`ID`),
   FULLTEXT KEY `fullSearch` (`title`,`author`,`blurb`)
) TYPE=MyISAM;

-- 
Gallery Robinson Web Services
http://galleryrobinson.com/