Loading...
 
Architecture / Installation

Architecture / Installation

Forum List Topic List

Forums » Architecture / Installation » [SOLVED] MySQL search engine or ElasticSearch engine, which one to use ?
 
Thread actions
PDF Print this page
  • Print all pages

    • [SOLVED] MySQL search engine or ElasticSearch engine, which one to use ?

    Posted by Bernard Sfez / Tiki Specialist posts: 8633 Israel

    Hello,

    I know that Manticore Search is coming but it is the future 😉

    In the meantime performances and servers resources have grown up to a point I'm not totally sure why I would use ElasticSearch instead of MySQL.

    I tried to find documentation to pin-point the differences:


    I couldn't really find something tangible why I would should one or the other;

    1. ElasticSearch brings faster search, better search
    Not sure or proved today with middle class Tiki (trackeritem: 7348, article: 885, file: 2234, user: 1824, wiki page: 2052)
    I tested both and have more issues with accents and partial term search with ES than with MySQL Search.
    2. Faceted search, which also be used through PluginCustomSearch
    Limited, complex (yes Jonny 🤣) and not always needed
    3. Date-based aggregations, Stored Search, Module More Like This, Federated Search
    Not always needed
    4. Natural Language Processing
    Unclear about what this does in an "Tiki standard" website

    In short a little gain and feature addition is not always needed.

    In regard the cost are real.
    Require Elasticsearch and admin it
    Require much more memory
    ...

    So why one will used ElastiSearch instead of MySQL ?

    Reads: 583
    Link
    Posted by Aris Bernotas posts: 31 Lithuania

    Some say that if you can't properly index your DB for ES and/or can't make proper queries, then there is no advantage.
    Btw, the answer from chatgtp:
    Elasticsearch and MySQL are both popular search engines, but they are designed to serve different purposes. Elasticsearch is a distributed search and analytics engine that is optimized for handling large volumes of unstructured data, while MySQL is a relational database management system that is optimized for handling structured data.

    When it comes to full-text search, Elasticsearch has several advantages over MySQL. Here are some key differences between the two:

    Text search capabilities: Elasticsearch is designed specifically for full-text search, and it has a rich set of features for indexing and searching text data. For example, Elasticsearch supports stemming, synonym expansion, and fuzzy matching, which can help to improve the accuracy of search results. In contrast, MySQL's full-text search capabilities are more limited, and it does not support some of the more advanced search features that Elasticsearch does.

    Speed: Elasticsearch is generally faster than MySQL when it comes to full-text search, especially for large datasets. This is because Elasticsearch is optimized for search, whereas MySQL is optimized for transactional processing. Elasticsearch uses inverted indices and other techniques to speed up search queries, while MySQL relies on traditional indexing methods.

    Scalability: Elasticsearch is highly scalable, and it is designed to handle large volumes of data across multiple nodes in a cluster. MySQL can also be scaled horizontally by adding more nodes, but it is not as well-suited for handling large amounts of unstructured data.

    Ease of use: Elasticsearch has a relatively steep learning curve, and it requires a good understanding of the underlying technology in order to use it effectively. MySQL, on the other hand, is more user-friendly and has a more intuitive interface.

    In summary, if you are working with unstructured data and need to perform complex text searches, Elasticsearch is likely to be a better choice than MySQL. However, if you are working with structured data and need to perform transactional processing, MySQL may be a more appropriate option.

    Link

    Posted by Bernard Sfez / Tiki Specialist posts: 8633 Israel

    Thank you Aris.

    What you wrote is exactly what I expect from chatGPT... It is the theory. 🤣

    What about real life ?
    People here really feel their Tiki work better, search better with ES than with MySQL ?

    Link
    Posted by Bernard Sfez / Tiki Specialist posts: 8633 Israel

    Thank you @marclaporte.

    One important information for those of us using extensively trackers I see on the page is:

    (MySQL Search)... There is a limitation on the number of tracker fields. The limitation is quite high (2000+), but when you hit it, you need to move to another engine because MySQL/MariaDB has a hard limit...

    However, this information on the page is 10 years old. 🤔

    I tested a 7400 trackeritems Tiki24 (my test Tiki, see above) with "mysql Ver 15.1 Distrib 10.3.35-MariaDB, for Linux (x86_64) using readline 5.1" and I didn't see, so far, any problem serving data (many plugin List and CustomSearch).

    Asking chatGTP, that was his answer:
    "in general, the maximum number of rows that can be stored in a single table in MySQL with InnoDB storage engine is determined by the maximum file size limit of the underlying file system. This limit is typically 2^64 bytes (16 exabytes) on most modern file systems.

    Assuming that you have enough disk space, the number of tracker items you can use will also depend on the size of each item and the total amount of memory available on your server. It's also worth noting that MySQL has a limit on the total size of data that can be stored in a single table, which is determined by the maximum value of the innodb_file_per_table configuration setting, which is set to 4GB by default."

    I don't have the skills or knowledge to deeply test and confirm or update the information on the page Unified Index Comparison.

    Link
    Posted by Marc Laporte posts: 1633 Canada

    The limitation is on the number of tracker fields (which use columns in the Unified Index).

    Tracker items use rows. The number of rows can be huge, but at one point, you'll reach performance problems, and this is where the other engines shine.

    Link

    Posted by Bernard Sfez / Tiki Specialist posts: 8633 Israel

    Thanks again for insights.

    I did some extensive test and yes on big Tiki, ElasticSearch is definitely faster and better.

    Simple word search may not show you the benefit of using ElasticSearch over MySQL but more complex search and using related features will show the differences.

    For example, indexing and searching for a term within a 72 page PDF stored in a Tiki file gallery will work fine and smoothly (fast, no need to rebuild the unified-search-index after uploading it) while, when using MySQL it is slow and somehow hazardous (some search attempt left me with a not found).

    Link