• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: MySQL: Moving to InnoDB
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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
  • Follow-Ups:
    • Re: MySQL: Moving to InnoDB
      • From: Kieran Kelleher <email@hidden>
References: 
 >MySQL: Moving to InnoDB (From: Timo Hoepfner <email@hidden>)
 >Re: MySQL: Moving to InnoDB (From: David LeBer <email@hidden>)
 >Re: MySQL: Moving to InnoDB (From: Kieran Kelleher <email@hidden>)

  • Prev by Date: Re: Modeling an optional to-one relationship
  • Next by Date: Re: MySQL: Moving to InnoDB
  • Previous by thread: Re: MySQL: Moving to InnoDB
  • Next by thread: Re: MySQL: Moving to InnoDB
  • Index(es):
    • Date
    • Thread