I have 3 fields
BLOG_comments - FULLTEXT index
BLOG_article - FULLTEXT index
BLOG_title - NO index
MY URL IS AS FOLLOWS
My blog title in the url is condenced to maximum of 12 characters and when I click on that link What my script does it this :
[color=#CC0000]$query = "SELECT * FROM blog AS b LEFT JOIN section AS s ON b.blog_group = s.section_id WHERE blog_ts BETWEEN unix_timestamp($year$month$day"."000000) AND unix_timestamp($year$month$day"."235959) AND blog_title LIKE '$topic%'";[/color]
But when I want to SEARCH for all fields it would be better to make all three fields FULLTEXT since it would not require going thru all fields
when I am in my comments page
SQL for that is like this:
[color=#CC0000]$query = "SELECT blog.blog_id,blog_title,blog_comm,comm.* FROM blog RIGHT JOIN comm ON comm.blog_id = blog.blog_id WHERE comm.blog_id = 1[/color]";
assumingly i DON't know my blog id but I don't! I just know my article name.. So I could do this
[color=#CC0000]$query = "SELECT blog.blog_id,blog_title,blog_comm,comm.* FROM blog RIGHT JOIN comm ON comm.blog_id = blog.blog_id WHERE comm.blog_title LIKE '$title%'[/color]";
That would work! but my field index is not FULLTEXT
the best way is to fulltext for all three, adjust the parameters for fulltext in that index, and then just match against that, but I don't know how to adjust the parameters ..
and if I search using full text it takes out the
arbritary works like AND THE and ect and any word that
appears in more than 50% of the records will not be
indexed. So I would Like not to use fulltext for that reason but if I know how to adjust the parameters. It will be cool.
I have this ugly query now
[color=#CC0000]SELECT * FROM articles WHERE MATCH (comm_body,blog_body) AGAINST ($search_str) OR blog_title LIKE '%$search_str%';[/color]
yea so not only I am pounding the index, which is fine, I'm also doing a full table scan which is not good!
any Ideas would be appreciated