List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:January 28 2005 8:32pm
Subject:Re: FULLTEXT index on InnoDB tables (looking for a workaround)
View as plain text  
symbulos partners wrote:
> Thanks for the extremely useful answer.
> 
> Some comments, questions here below.
> 
> On Friday 28 Jan 2005 16:38, you wrote:
> 
>>No, only the BLOB/TEXT columns need to be moved to MyISAM.
> 
> 
> Yes, I thought of doing so. The drawback is that you de-normalise the 
> database. Is that correct?

No, I believe if the database was in the third normal form, it would still be. 
You just split the entity into two sub-entities that logically share the primary 
key.

But that does not really matter. Normalization is a theory. If using it helps 
you create an application that is fast, uses less resources, and is easy to 
maintain then stick to it. If it gets in the way of reaching your goals, it is 
not the right theory for your application.

> 
> There are 2 alternatives we should then consider:
> - extract the two important columns, put them in a separate MyISAM table, 
> index the separate table, operate searches only on the separate table. Like 
> having a "view", but permanent.
> - separate the original table, in two tables (columns which need to be 
> indexed, columns which do not need to be indexed), operate the search only on 
> the table with the relevant columns. I do not like it too much, because it 
> spoils the structure of the database.
> 
> From a logical point of view, the former is better. Furthermore, there is 
> rollback. (By the way, how do you solve the rollback problem?)

Now you are asking difficult questions. MyISAM tables do not know about 
rollback, so you have to fake it, but you never have a real one. You can try to 
take care of it in your application by deleting or restoring the modified rows.

But if I were in your shoes, I would ask at this point about how big of a deal 
it is in your application to be able to roll back your blob.

And, of course, another option is to contact Heikki and coax him into hiring 
somebody to add FULLTEXT to InnoDB tables.




-- 
Sasha Pachev
AskSasha Linux Consulting
http://www.asksasha.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