Re: MySQL: Moving to InnoDB
Re: MySQL: Moving to InnoDB
- Subject: Re: MySQL: Moving to InnoDB
- From: Timo Hoepfner <email@hidden>
- Date: Thu, 14 Jan 2010 15:45:03 +0100
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 (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden