List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 28 2005 5:03pm
Subject:Re: FULLTEXT index on InnoDB tables (looking for a workaround)
View as plain text  
> > > 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.

Then again - this particular problem is more a MySQL internal
problem that simply should be fixed :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

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