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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
