"Scott Purcell" <[EMAIL PROTECTED]> wrote on 07/29/2005 04:44:10 PM:
> Hello, > > I have created a table for items. And I needed some of the columns > to be searchable, so I created the table as: > a MyISAM for full text search. > > CREATE TABLE `item` ( > `parent_id` int(11) NOT NULL default '0', > `id` int(11) NOT NULL auto_increment, > `manufacturer_id` varchar(50) default NULL,, > `limited_qty` char(1) NOT NULL default 'N', > `link_name` varchar(50) default NULL, > PRIMARY KEY (`id`), > UNIQUE KEY `index_id` (`id`), > FULLTEXT KEY `description` (`description`), > FULLTEXT KEY `name` (`name`), > FULLTEXT KEY `name_2` (`name`), > FULLTEXT KEY `name_3` (`name`,`description`) > ) TYPE=MyISAM; > > > The above table item has a primary key of ID. since this is an 'item' > I have 2 other tables that have references to this item: > item_cart_ref and item_category_ref. > > I needed to create foreign keys on those two tables, but they are > type InnoDB because of the foreign key. > > CREATE TABLE cart_ref_items ( > cart_id varchar(50) not null, > item_id int not null, > usertype varchar(20) NOT NULL DEFAULT 'anonymous', > quantity int default 1, > key(item_id), > FOREIGN KEY (item_id) references ITEM(id) on DELETE CASCADE > ) TYPE=InnoDB > > > CREATE TABLE ITEM_CAT_REL ( > id INT, > cat_id INT NOT NULL, > key(id), > FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE > ) TYPE=InnoDB > > > But I cannot mix types, it errors: > > So how can one use foreign key references to their main table when > it needs to have full-text search? > > > Help > Scott > Fulltext searching in InnoDB is on the TODO list. Until then you can design your data in Innodb (so that you can create and use foreign keys) with one exception. A simple table (two columns is usually enough, I think you will need one with five) will need to be created in MyISAM to contain the TEXT field(s) you want to FT search. One column contains the PK value of the table you maintain in InnoDB while the other column(s) contains the TEXT. Yes it's a bit of an overhead but since none of the BIG MONEY is paying to make FT+InnoDB a priority, we lowly "community" users have to work around the lack of funcitonality until it makes it through the development pipeline. I think that it is a fair compromise. One "straggler" table to be able to do FT searches is not too much to ask or maintain with your application code outside of the FK protections. Nobody said MySQL was perfect but it is REALLY GOOD, ya know? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
