symbulos partners <partners@stripped> 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
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
> allow full text search?
Not for InnoDB tables.
> symbulos partners
> symbulos - ethical services for your organisation
Unimin Corporation - Spruce Pine