Page 1 of 1

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

Posted: Thu Aug 24, 2006 1:54 am
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

Posted: Thu Aug 24, 2006 3:11 am
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?

Posted: Thu Aug 24, 2006 3:35 am
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.

Posted: Thu Aug 24, 2006 9:20 am
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.

Posted: Fri Aug 25, 2006 1:59 am
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

Posted: Fri Aug 25, 2006 8:26 pm
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.

Posted: Fri Aug 25, 2006 9:06 pm
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.

Posted: Fri Aug 25, 2006 11:12 pm
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.

Posted: Sat Aug 26, 2006 4:54 am
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.

Posted: Sun Aug 27, 2006 12:07 am
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??

Posted: Sun Aug 27, 2006 5:17 am
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.