Clever. Thanks for sharing. Regards, Kieran :-)
On Jan 14, 2010, at 9:45 AM, Timo Hoepfner wrote: > Ok, went a bit different way. I added a myisam table and added triggers for > insert/update/delete on the (now) innodb table to put a copy in the myisam > table which now has the fulltext index. As the app is read-mostly, the > additional load is not a problem. The PHP query needed to be adjusted to use > the other table for the fulltext search. > > For the record, this is basically what I did: > > ALTER TABLE `products` DROP INDEX `fulltextDE`; > ALTER TABLE `products` DROP INDEX `fulltextEN`; > ALTER TABLE products ENGINE=InnoDB; > > DROP TABLE IF EXISTS `_fulltext`; > CREATE TABLE `_fulltext` ( > `id` INT(11) NOT NULL, > `tbl` varchar(255), > `name` varchar(255), > `nameDE` varchar(255), > `nameEN` varchar(255), > `description_de` TEXT, > `description_en` TEXT, > PRIMARY KEY (`id`), > FULLTEXT KEY `fulltextDE` (`name`,`nameDE`,`description_de`), > FULLTEXT KEY `fulltextEN` (`name`,`nameEN`,`description_en`) > ) ENGINE=MyISAM; > > INSERT INTO _fulltext (`id`, `tbl`, `name`, `nameDE`, `nameEN`, > `description_de`, `description_en`) SELECT `id`, "products", `name`, > `nameDE`, `nameEN`, `description_de`, `description_en` FROM products; > > delimiter // > > DROP TRIGGER IF EXISTS products_i;// > CREATE TRIGGER products_i AFTER INSERT ON products > FOR EACH ROW > BEGIN > INSERT INTO _fulltext (`id`, `tbl`, `name`, `nameDE`, `nameEN`, > `description_de`, `description_en`) VALUES (NEW.id, "products", NEW.name, > NEW.nameDE, NEW.nameEN, NEW.description_de, NEW.description_en); > END;// > > DROP TRIGGER IF EXISTS products_u;// > CREATE TRIGGER products_u AFTER UPDATE ON products > FOR EACH ROW > BEGIN > UPDATE _fulltext SET tbl="products", `name`=NEW.name, `nameDE`=NEW.nameDE, > `nameEN`=NEW.nameEN, `description_de`=NEW.description_de, > `description_en`=NEW.description_en WHERE `id`=NEW.id; > END;// > > DROP TRIGGER IF EXISTS products_d;// > CREATE TRIGGER products_d AFTER DELETE ON products > FOR EACH ROW > BEGIN > DELETE FROM _fulltext WHERE id=OLD.id; > END;// > > delimter ; > > Thanks a lot for your suggestions. Now for the mysql optimizations... > > Timo > > Am 14.01.2010 um 04:08 schrieb Kieran Kelleher: > >> Hi Timo, >> >> If you mix MyISAM and InnoDB, then when a transaction fails, IIRC, just he >> InnoDB tables will rollback, the MyISAM tables will not. If important,to >> you, then you could potentially write some logic that if an ec save fails, >> then do some logic to delete the rows (EOs) inserted into the MyISAM tables. >> Also, perhaps some schema changes might allow the FULLTEXT search columns to >> be broken out into separate related tables in a similar fashion to how one >> might break out a BLOB image attribute into a separate table from an >> entity's table to prevent it unecessarily being hydrated from a fault into a >> real EO. This is just an idea ... I have not had to deal with your >> situation, but this is a possible approach: >> >> >> BEFORE >> Entity1 (maps to MyISAM table) >> attr1 >> attr2 >> fulltextattr >> >> AFTER >> Entity1 (maps to innodb table) >> attr1 >> attr2 >> FK <<---> Entity2 (maps to myisam table) >> PK >> fulltextattr >> >> >> The java logic of Entity can easily be changed so that the get and set pull >> the fulltextattr from the related eo. >> >> >> The advantage is that if a transaction fails halfway, you end up with a >> Entity2 in the DB and no corresponding Entity1. You can delete these orphans >> if an ec fails to save and the PHP logic can ignore search results where >> Entity2 has no related Entity1's. >> >> HTH, Kieran >> >> On Jan 13, 2010, at 4:01 PM, David LeBer wrote: >> >>> >>> On 2010-01-13, at 3:52 PM, Timo Hoepfner wrote: >>> >>>> Hi list, especially Kieran. :) >>>> >>>> I have a project that evolved over the years. Basically it's a product and >>>> image database, which also provides the content for the customer's home >>>> page. The product and image database and CMS administration is done in WO, >>>> the customer's home page was coded in PHP by someone else (other company), >>>> accessing the same DB (read only). Using MySQL was a customer requirement >>>> when the project started. >>>> >>>> The PHP stuff depends on MySQL's FULLTEXT search (with a custom stopwords >>>> configuration), which in turns requires the use of MYISAM. I'd really like >>>> to move the whole DB to InnoDB tables, but I have no clue how to keep the >>>> FULLTEXT stuff of the PHP part functional. >>>> >>>> It would be no problem to change the WO or PHP code (for which I now also >>>> have the honor to maintain) as long the stuff continues to work as >>>> expected. >>>> >>>> Any ideas? >>> >>> Timo, >>> >>> I know it's ugly, but you can mix InnoDB and MyISAM tables in the same >>> database. >>> >>> If the FULL TEXT indexes are only on a single/few tables/columns that may >>> be an option. >>> >>> Keep MyISAM for the FT tables, move everything else to InnoDB. >>> >>> ;david >>> >>> -- >>> David LeBer >>> Codeferous Software >>> 'co-def-er-ous' adj. Literally 'code-bearing' >>> site: http://codeferous.com >>> blog: http://davidleber.net >>> profile: http://www.linkedin.com/in/davidleber >>> twitter: http://twitter.com/rebeld >>> -- >>> Toronto Area Cocoa / WebObjects developers group: >>> http://tacow.org >>> >>> >>> >>> >>> _______________________________________________ >>> Do not post admin requests to the list. They will be ignored. >>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>> Help/Unsubscribe/Update your Subscription: >>> http://lists.apple.com/mailman/options/webobjects-dev/kieran_lists%40mac.com >>> >>> This email sent to kieran_li...@mac.com >> > _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
