considerations on the type of tables used and on search functionality

[JohnRDOrazio]

FULLTEXT SEARCH CAPABILITIES

Since fulltext search is an important aspect of this project, this needs to be taken into consideration. Before MySQL 5.6.4, only MyISAM databases had fulltext search capability, however ever since MySQL 5.6.4 (2011-12-20), InnoDB also has fulltext search capability. Some say to avoid using MyISAM for fulltext search now since it can get corrupted, and to prefer InnoDB.

• Would Lucene / ElasticSearch / Xapian / Apache Solr be better options for text search capabilities, rather than relying on the database? ElasticSearch and Solr are both based on Lucene. All these solutions support stemming, which can be a very useful feature for finding words that have the same root.
  (Solr is a search server for creating standard search applications, no massive indexing and no real time updates are required, but on the other hand Elasticsearch takes it to the next level with an architecture aimed at building modern real-time search applications. While Solr has traditionally been more geared toward text search, Elasticsearch is aiming to handle analytical types of queries.)
  • From the Apache Lucene website: Lucene Core is a Java library providing powerful indexing and search features, as well as spellchecking, hit highlighting and advanced analysis/tokenization capabilities. The PyLucene sub project provides Python bindings for Lucene Core.
  • From the same website: Solr^TM is a high performance search server built using Lucene Core. Apache Solr is highly scalable, providing fully fault tolerant distributed indexing, search and analytics. It exposes Lucene's features through easy to use JSON/HTTP interfaces or native clients for Java and other languages.
  • ElasticSearch is a distributed RESTful search engine built on top of the Lucene library and useful for cloud implementations, though it can also be self-hosted. It is document oriented and offers near real-time search performance. Each shard is a fully functional Lucene index. Elasticsearch supports multiple indices. You can configure each index differently, with a varying number of shards. Each index is broken down into shards, and each shard can have one or more replicas. By default, an index is created with 1 shard and 1 replica per shard (1/1). There are many topologies that can be used, including 1/10 (improve search performance), or 20/1 (improve indexing performance, with search executed in a map reduce fashion across shards).
  • Xapian is an Open Source Search Engine Library, released under the GPL v2+. It's written in C++, with bindings to allow use from Perl, Python 2, Python 3, PHP 5, PHP 7, Java, Tcl, C#, Ruby, Lua, Erlang, Node.js and R (so far!) Xapian is a highly adaptable toolkit which allows developers to easily add advanced indexing and search facilities to their own applications. It has built-in support for several families of weighting models and also supports a rich set of boolean query operators.

FOREIGN KEYS / INTERNAL RELATIONS

Seeing that it's not useful to keep the names of the books of the Bible in the tables that have the verse texts, as that would take up extra space unnecessarily, the tables are designed in such a way to have a separate table for the names of the books of the Bible in different languages alongside an identifying number which is used in the tables with the verse texts. Then a relationship needs to be defined in order to substitute that number with the name of the book.

• MyISAM does not support relationships internally, so it does not allow for "FOREIGN KEY" constraints. However PHPMyAdmin uses it's own "controluser" internal tables to define relationships in this case. So internal relationships can be defined in PHPMyAdmin, however they cannot be exported along with the tables

CURRENT SITUATION

I believe that when I started the BibleGet project, InnoDB didn't yet support fulltext search, so I chose to use MyISAM tables. I have Fulltext search enabled and it seems to be working well, it's also quite fast. I haven't had any trouble with corruption of data but that could also be because no data is added to or removed from these tables, the tables are static and so the indexes are not updated. I have defined the "internal relationships" offered by PHPMyAdmin, which are easy enough to use (actually more intuitive than Foreign Key constraints in my opinion!).
Would there be any real advantage to switching from MyISAM to InnoDB? It's not like these tables need to be exported / imported very much. And even if it were, say to create a backup and restore, it's not like there are all that many relationships to restore, so doing it manually really wouldn't be all that difficult...

[JohnRDOrazio]

Here's another consideration: MyISAM performs full table locking for update operations, whereas InnoDB performs row-level locking. This means that InnoDB has higher performance when multiple read and write operations are performed on the table, whereas MyISAM has higher performance when only reading from the table is required (it doesn't have to scan to check the row locks).
So in conclusion: for the tables with the Bible texts and indexes, stick with MyISAM, it should offer slightly better performance.

[JohnRDOrazio]

Considering the above observation, this would also mean that the requests log table for the current year (and for future years basically) should use the InnoDB storage engine, seeing they will be doing multiple read-write operations, whereas the requests log table for past years can be switched over to use the MyISAM storage engine, since no more write operations will be performed on them.