[thelist] Fun with MySQL

[Hershel Robinson]

 > 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

I actually saw that post but I didn't study it close enough to realize 
that he had actually solved the same problem that I am facing. Thanks.

> How about an alternative?  Skip the temp table:
> 
> MySQL v4 and up allow UNION clauses.  Expanding on Anthony's suggestion, 
> you might end up with something like this:
> 
> (SELECT ID, author, title, blurb, pageNum,
>    MATCH (author,title,blurb) AGAINST ('Weiss') AS relevance
>    FROM `indexes`
>    WHERE MATCH (author,title,blurb) AGAINST ('Weiss'))
> UNION ALL
> (SELECT ID, author, title, '' as blurb, 0 as pageNum,
>    MATCH (author,title) AGAINST ('Weiss') AS relevance
>    FROM `radio_details`
>    WHERE MATCH (author,title) AGAINST ('Weiss'))
> ORDER BY relevance DESC

Now this is even better. I had tried a UNION at one point, but it didn't 
work easily, so I left it. But this solution is perfect. Thank you VERY 
much.

Well, it's seem as perfect as one could get. The only problem left is 
that the indexes table has 70 times the number of rows that the 
radio_details table does. It seems from MySQL that the relevance value 
for each row is based on the number of rows found, so if more rows are 
found in one table, the those values will be different than for the 
other table. I ran two tests, and the relevance for indexes hovers 
around 2, but the relevance for radio_details is just under 1, so in 
practice, the radio_details come out last anyway. :)

Perhaps this will change as the radio_details table grows...

> ps.  I suspect you already know, but mysql_escape_string() would be 
> appropriate on $_REQUEST.

Yes, you are correct. Whoever originally wrote this code didn't quite 
realize that. I will fix it, thanks. :)

Hershel


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