Full-Text Search with table of type "INNODB"??????

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Full-Text Search with table of type "INNODB"??????

Post by mcccy005 »

Is it possible to create a FULLTEXT index in a table of type "INNODB"?? If not (as the mysql website says), how the hell is it possible to implement a full-text search when your table has foreign keys in it (as every decent normal database should)????
I'm new to this whole searching thing as of today so trying to get my head around it all. Any links to decent tutorials would also be appreciated.

Cheers
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

It is not possible to create a FULLTEXT Index in InnoDB at this time. Why not create a 2 Column MyISAM table that has just the search field and a key linking it back to your InnoDB table?
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

How can I create a key linking back to the INNODB table if you cant use referencing in MYISAM tables??
(IF i properly understand what you're saying; which i probably dont).
Thanks.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

A foreign key reference in MYISAM tables is simply a column that matches the type of the primary key to the table you wish to reference. They are manually kept up-to-date, instead of automated.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

If the tables having InnoDB is for searching online and you don't have to put the data from the online users, convert the InnoDB table to MyISAM with FullIndex feature. which is faster. If not you can create two tables having MyISAM and InnoDB storage engine like jamiel said and you can query the search only in MyISAM table. The only problem in MyISAM is lacking refential integrity and have to do that manullay like feyd said.

Cheers,
Dibyendra
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Hmm.....problem is I have so many references in my database and users are responsible for updating much of the information over the web!! Other problem is I need to search a lot of the indexed columns aswell!!

I suppose an alternative would be to create a duplicate database, setting every table in the duplicate to MYISAM and capturing queries to the primary database; parsing them to the duplicate database; and subsequently allowing a search of the duplicate database??
This way I would have a backup (of information at least) of the primary (original) database?? WOuld this work?? (Obviously I would have to take out the index's in the MYISAM tables and would have lots of duplicate data but that seems unavoidable anyways.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

If you are not making use of FOREIGN KEY REFERENCES and CASCADEs on your InnoDB tables you should be able to safey change them to MyIsam and use FULLTEXT. If you are (which you should be because otherwise there really isn't any point in InnoDB) use a LIKE clause. This will pull more results than you want because it matches parts of words instead of whole words. But you can then use some PHP (or clever SQL) to make sure you only deal with the word matches. This is just an idea. I'm not sure whether it is a particularly good one but I do know that it would work.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Yes I am using plenty of foreign key references.

I have considered using the "like" feature, but the problem with this is if the users want to search for multiple words! Eg. If they are searching a database of boats and search for "yacht hawaii" then I want to pull results with boats that are either yachts, or located in hawaii AND also put boats that ARE yachts IN hawaii highest up in the result list; and I like the idea of being able to implement boolean type searches.
ALso if the user searches the name of a boat, this may be 2 words so i have to search the database for the entire phrase; then search again for each individual phrase which is a lot of processing.

Thanks.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

I'd probably:

Code: Select all

LIKE 'boats' OR LIKE 'hawaii';
and do the rest in PHP. Its a bit of work sure.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Guess thats probably the best idea for the time being!!

And if I get duplicate results, I can use select 'UNIQUE' or something like that cant I??

Cheers.

PS. IS anybody working on implementing fulltext search on the INNODB databases or is anybody working on implementing references in MYISAM tables that your aware of??
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

And if I get duplicate results, I can use select 'UNIQUE' or something like that cant I??
Yeah probably. You might be after DISTINCT.
Post Reply