symbulos partners <[EMAIL PROTECTED]> wrote on 01/28/2005 11:14:00 AM:
> On Friday 28 Jan 2005 15:41, you wrote: > > As you have noticed - you cannot. > > > > So, all you can do is creating a MyISAM table and copying > > the column contents. > > Are you suggesting to have a full copy of the table in MyISAM format? No, only the BLOB/TEXT columns need to be moved to MyISAM. > > Is there any other workaround? The reason because we are using InnoDB is > because there s full support > - for foreign keys, > - for joint queries > - for rollback on commit > The MyISAM table type also fully supports JOIN queries. More importantly, for you, it supports full-text indexes. What many people have done to solve the problem you present has been to split the original table into two pieces. All BLOB/TEXT fields and a field ID are moved to a MyISAM table while the other fields stay in your original InnoDB table. This has a distinct performance advantage, too. If you run a query that retrieves only non-(BLOB/TEXT) fields from a table that has BLOB/TEXT columns defined (any table type), then all of the BLOB/TEXT data is read with the rest of the row data off of the disk into memory for every row not eliminated by an index, just to be ignored for the final output. By splitting your "heavy" fields into a separate table, you will avoid transferring all of that data into memory each and every time you only need "light" data (numerics, chars, varchars, etc) from the row. This results in less disk I/O, less memory consumption, and fewer CPU cycles for each and every query that doesn't need to use or retrieve the BLOB/TEXT data. What you lose with this design is, as you pointed out, the ability to rollback changes to the text fields when a transaction fails. You could not define foreign keys on BLOB/TEXT fields anyway(only against portions of those fields) so you are not losing much that way. However since BLOB/TEXT data is usually not as frequently updated as the other fields, you may be just fine without that level of protection. > Does anybody know any other way of indexing the table in a way, which would > allow full text search? Not for InnoDB tables. > -- > symbulos partners > -.- > symbulos - ethical services for your organisation > http://www.symbulos.com > Shawn Green Database Administrator Unimin Corporation - Spruce Pine
