NYCPHP Meetup

[Ben Sgro (ProjectSkyline)]

Hello Again,


"    That's the beauty of it -- a mySQL fulltext index is maintained
automatically.  Once it is created,  it is automatically updated when
you insert,  update and delete rows,  just like any other index."

- or -

> My understanding (I could be wrong) is that every time you add more data, 
> you have to rebuild the index to be able to use FULL TEXT. However, that 
> doesn't need to be done until the next search.

Which is it? If its the first one, that's great, but what if I'm doing a ton 
of inserts
throughout the day...Will the database be locked while its rebuilding the 
index?
How long will this take? I should have around 1 million records, but since 
the data
goes stale after about 5 days, I can prune the database. So, I should never 
have
much over or under 1million records.

My data is being populated by a cron job and could insert 100's of new rows 
every day, throughout the entire day..
is this going to be a problem?


> So, what I'm thinking is to set a dirty flag that will be true if data has 
> been added and false if the index is up to date and then have the indexing 
> triggered by a search request. However, I'm still working on it, so there 
> may be better ways to do this.

That sounds good, but I dont want a single user to wait 20m while
the index file is rebuilt....

- Ben

----- Original Message ----- 
From: "tedd" <tedd at sperling.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Cc: "Ben Sgro (ProjectSkyline)" <ben at projectskyline.com>
Sent: Monday, April 16, 2007 10:16 AM
Subject: Re: [nycphp-talk] SQL Full text searcing and storing


> At 9:21 AM -0400 4/16/07, Ben Sgro \(ProjectSkyline\) wrote:
>>
>>One question I still have is about building/rebuilding the indexes.
>>Is this done when new fields are added to the database?
>>Or a routine that needs to be done more often?
>
> My understanding (I could be wrong) is that every time you add more data, 
> you have to rebuild the index to be able to use FULL TEXT. However, that 
> doesn't need to be done until the next search.
>
> So, what I'm thinking is to set a dirty flag that will be true if data has 
> been added and false if the index is up to date and then have the indexing 
> triggered by a search request. However, I'm still working on it, so there 
> may be better ways to do this.
>
> Anyone care to comment?
>
> Cheers,
>
> tedd
>
> -- 
> -------
> http://sperling.com  http://ancientstones.com  http://earthstones.com