Re: FULLTEXT index on InnoDB tables (looking for a workaround)
All, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, January 30, 2005 6:29 AM Subject: Re: FULLTEXT index on InnoDB tables (looking for a workaround) At 02:32 PM 1/28/2005, you wrote: And, of course, another option is to contact Heikki and coax him into hiring somebody to add FULLTEXT to InnoDB tables. And bring lots of cash with you. :-) I've pestered Heikki a couple of years ago and he's willing to do it only if there is someone willing to fund it. So far it doesn't look like there have been any takers. If I win the lottery, this is going to be in my top 10 list. g we have found a sponsor. http://www.innodb.com/todo.php We are trying to hire a new developer for this. Mike Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
At 02:32 PM 1/28/2005, you wrote: And, of course, another option is to contact Heikki and coax him into hiring somebody to add FULLTEXT to InnoDB tables. And bring lots of cash with you. :-) I've pestered Heikki a couple of years ago and he's willing to do it only if there is someone willing to fund it. So far it doesn't look like there have been any takers. If I win the lottery, this is going to be in my top 10 list. g Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
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? 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 Does anybody know any other way of indexing the table in a way, which would allow full text search? -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners [EMAIL PROTECTED] 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
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
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? 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?) The latter consumes less disk space, performance wise is better. Do you see any other drawback / advantage? -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
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? 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?) The latter consumes less disk space, performance wise is better. Do you see any other drawback / advantage? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners wrote: 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 Does anybody know any other way of indexing the table in a way, which would allow full text search? Sure -- use an external search engine that has database hooks (or create your own connector). Depending on the sophistication (or existence) of a database connector for the search engine, you'll have to write more or less code to tell it how them to talk to each other -- how to know when there's new data to index, how to retrieve the text data into the search engine for indexing or display. The most sophisticated ones use database triggers to make it all fairly easy. Otherwise, you'll need to write code that hands the text and a pointer (typically the primary key) to the full-text engine when a record is added or modified, and the pointer for deletes. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
