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
