List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 28 2005 4:38pm
Subject:Re: FULLTEXT index on InnoDB tables (looking for a workaround)
View as plain text  
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 
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
Thread
FULLTEXT index on InnoDB tablessymbulos partners28 Jan
  • Re: FULLTEXT index on InnoDB tablesMartijn Tonies28 Jan
    • Re: FULLTEXT index on InnoDB tables (looking for a workaround)symbulos partners28 Jan
      • Re: FULLTEXT index on InnoDB tables (looking for a workaround)SGreen28 Jan
        • Re: FULLTEXT index on InnoDB tables (looking for a workaround)Martijn Tonies28 Jan
        • Re: FULLTEXT index on InnoDB tables (looking for a workaround)symbulos partners28 Jan
          • Re: FULLTEXT index on InnoDB tables (looking for a workaround)Sasha Pachev28 Jan
            • Re: FULLTEXT index on InnoDB tables (looking for a workaround)mos30 Jan
        • Re: FULLTEXT index on InnoDB tables (looking for a workaround)corradoerina@btopenworld.com28 Jan
      • Re: FULLTEXT index on InnoDB tables (looking for a workaround)Nick Arnett28 Jan
Re: FULLTEXT index on InnoDB tables (looking for a workaround)Heikki Tuuri30 Jan