fulltext index problem Mysql/php

Bhoomika

😕 Help in php/mysql

using
php: 4
mysql :3.23
Apache- 2.0.45 (Win32) Server

I m working on a Books and articles search engine its populated with 200 records.
table contains title, authorname, journal name , keywords, transaction id, articles id, year, vol no. ,issue no. etc.

title, auname, journal name and keywords are indexed (fulltext).

TABLE indexarticles (
tid int(4) unsigned NOT NULL auto_increment,
aid int(4) unsigned NOT NULL default '0',
auid int(4) unsigned NOT NULL default '0',
au_name varchar(30) NOT NULL default '',
title varchar(255) NOT NULL default '',
jname varchar(255) NOT NULL default '',
year year(4) NOT NULL default '0000',
keyword varchar(100) NOT NULL default '',
vol varchar(10) default '0',
issue varchar(10) default '0',
PRIMARY KEY (tid),
FULLTEXT KEY au_name (au_name),
FULLTEXT KEY jname (jname),
FULLTEXT KEY title (title),
FULLTEXT KEY keyword (keyword),
FULLTEXT KEY articlesinx (au_name,title,jname,keyword)
) TYPE=MyISAM

problme is

in my database almost 150 something records contains marketing or information word.

-->when i run bellow query i don't get any result.
select distinct aid from indexarticles where (match(title)
agains ('marketing'))

-->with bellow query its showing me all records containing word
marketing
select distinct aid from indexarticles where (title like'%
marketing%')

-->same for "information "
select distinct aid from indexarticles where (match(title)

against ('information'))

except this two other words are working fine.

Can anybody tell me what is the problem .

any help will be greatly appreciated

thanks

Bhoomika

LordShryku

From the manual:

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

The search for the word MySQL produces no results in the above example, because that word is present in more than half the rows. As such, it is effectively treated as a stopword (that is, a word with zero semantic value). This is the most desirable behaviour -- a natural language query should not return every second row from a 1 GB table.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search

Bhoomika

thanks friend

I know this.
but i want it to be detected, for that what should i do?

bhoomika

LordShryku

Since it exists in more than half hte table, stop using match, and start using like %keyword%

Bhoomika

Is it the only option.

will this( %keyword%) work fine with thousand of records.
will this make my search engine slow..?

equally in my search engine user can make there own query ?

the query can be three times bigger than bellow.

select distinct aid from indexarticles where (title like'%Marketing%' or au_name like'%Marketing%' or keyword like'%Marketing%' or jname like'%Marketing%') AND (title like'%of%' or au_name like'%of%' or keyword like'%of%' or jname like'%of%') AND (title like'%infromation%' or au_name like'%infromation%' or keyword like'%infromation%' or jname like'%infromation%') AND (title like'%and%' or au_name like'%and%' or keyword like'%and%' or jname like'%and%') AND (title like'%generation%' or au_name like'%generation%' or keyword like'%generation%' or jname like'%generation%') AND (title like'%of%' or au_name like'%of%' or keyword like'%of%' or jname like'%of%') AND (title like'%income,%' or au_name like'%income,%' or keyword like'%income,%' or jname like'%income,%') AND (title like'%The%' or au_name like'%The%' or keyword like'%The%' or jname like'%The%') AND (title like'%LRDC%' or au_name like'%LRDC%' or keyword like'%LRDC%' or jname like'%LRDC%') AND (title like'%experience%' or au_name like'%experience%' or keyword like'%experience%' or jname like'%experience%')

please guide me.

thanks

Bhoomika

srikanth1184

%keywork% works fine! But definately slow down your query.
I guess REGEXP should help you.

Pattern_matching DOCS