Geek Answers Handbook

MySQL Storage Engine Dilemma

There are two MySQL database functions that I want to use in my application. The first is FULL TEXT SEARCH AND OPERATIONS.

Now the dilemma is that I cannot get this function in one repository. I either use MyIsam (which has the FULL-TEXT-SEARCH function), or I use InnoDB (which supports the TRANSACTION function). I can’t have both.

My question is, is there anyway I can have both functions in my application before I have to make a choice between two storage systems.

+7
source share
5 answers

Possible workarounds:

  • Use Sphinx or Solr or another external text search engine to search for text and use the InnoDB engine.

  • Write your own code - and use InnoDB. In fact, this is not an option if you do not need to find, are limited or your budget is huge.

  • Use both mechanisms, MyISAM and InnoDB. Save the columns you want to use for full-text search in MyISAM and the rest in InnoDB. This will be dangerous since the data in MyISAM will not be secure in the transaction.

  • Use both mechanisms, MyISAM and InnoDB. Store all the data in InnoDB and duplicate the columns you want to use for full-text search in MyISAM. This will require some mechanism (triggers) to duplicate data.

  • Wait for the version of MySQL where full-text search will be supported by InnoDB or another transaction mechanism.

  • (option 4), but use MariaDB (MySQL fork), which has full-featured text indexes with "safe for security" (but still not transactional): When-will -transactional-full-text-indexes-be ready?

  • Use other DBMSs, such as PostgreSQL, which have full-text support in the transaction engine.

+10
source

If you need to perform transactions and full-text on a single table in MySQL, you have several options. But in fact, the main point that should be distracted from all this discussion is that databases do not perform full-text searches very well (especially MySQL!), And ideally you want to offload this work to a component that performs this type of task better.

Option 1:

Create one table in which you need to complete the transaction as InnoDB, and then create another mirror table, which is MyISAM, with which you can perform full-text searches. You can synchronize data using a trigger in the InnoDB table. Kind of hacking, but it will work.

Option 3:

Take a look at a third-party full-text engine such as Sphinx , Lucene or Solr . Thus, you can focus on developing your database to be optimal when querying data, rather than forcing it to perform a text search.

Option 3:

You can select a different database server that supports transactions and full-text searches at the same time, such as SQL Server.

Hope this gives you some clarity.

Enjoy it!

+5
source

The MyISAM full-text index is probably not as good as you think. It works fine (ish) on small data, but on big data it is disgusting.

In MySQL 5.6, we may have full-text on InnoDB, but it still does not support most of the functions that real full-text search engines can have.

+3
source

there is no way to have a database in one engine, these are the restrictions set by the design of how MySQL works. You cannot change physics .; -)

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html

when you are still under development, you can also consider using another SQL application for your project, which supports both in one engine. For example, Postgres does.

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009#Transactions_and_the_Database_Engine_Core

+2
source

Another option is to use MySQL support for replication:

For example, you can configure the core server using the InnoDB storage engine. Then replicate to another read-only server with the MyISAM storage engine.

You can use almost any MySQL storage engine, and there may be some that support better search functions than MyISAM for some use cases.

+1
source

More articles:


All Articles