On Mon, 2006-08-07 at 09:49 +0100, Lexington Luthor wrote: > Peter Zaitsev wrote: > > Hi, > > > > One of approaches is to have "shadow" MyISAM table in addition to your > > Innodb table to perform full text search, which you can update in bulks > > or via triggers. > > How can I ensure isolation for queries on this "shadow" table? The > documentation says that the table type does not support transactions.
Right. If you want full text search to follow transaction isolation as well you're in trouble. In most search applications however it is not that critical. For some cases some extra filtering (ie by join with Innodb table) can help to ensure row versions match each other. If even that one would not work you would need to implement your own little search engine in SQL (ie creating dictionary table + word list table) - with this one you can make it to follow transaction isolation but it will be very slow. > Sorry for being a bit dense here, but what do you mean exactly? Will > updates to the "shadow" table only be visible in their own transaction > until commit? Will they be rolled back on transaction abort? No. MyISAM does not support transactions. So you would need to ensure shadow table updates handle it in some way. > > > You also can try sphinx: http://www.sphinxsearch.com/ which works with > > any storage engine and also much faster. > > From what I can tell from the Sphinx docs, it is not transactional > either. Not only that, it does not support SQL tables at all, it is > simply a wrapper for a search API using the mysql storage engine > interface. Can you please elaborate on what you mean? Right. I assumed you want to use Innodb tables because you want transactions but you did not really need search queries to follow same isolation mode. -- Peter Zaitsev, MySQL Performance Expert MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
