Kakšen/kater software (ali SQL rešitev) za hitrejši "full text search"?

Ne.

Lahko pogledaš kaj se trenutno dogaja z Lucene - če se ne motim je to engine za Elastic Search. Vem, da obstaja port v c# (Lucene.NET) in lahko sam vse postaviš. ES pa lahko uporabljaš direktno kot out of the box servis v kakšnem dockerju in ti je java in vse ostalo povsem skrito...

Pa si poskusil z innodb_ft_min_token_size = 2? Zakaj bi iskal po eni črki, saj to vrne praktično vse.

Jest sm leta nazaj nucal sphinx na ogromni bazi pesmi besedil in je bil search ter sortiranje instantno. Res da niso bli to terabajti podatkov, sam par 100GB je pa blo.

baje da lasten produkt

Alternativa MySQL

Percona Server for MySQL and MySQL are both popular choices for database management systems, but they offer different features and performance characteristics that may make one more suitable than the other for specific use cases.

**Performance and Scalability:**
- Percona Server for MySQL is designed to offer superior performance, scalability, and instrumentation compared to MySQL. It is trusted by many enterprises for its ability to handle demanding workloads efficiently.
- Percona Server includes enhancements such as InnoDB Resource Groups, configurable page sizes, contention-aware transaction scheduling, and improved scalability by splitting mutexes, which are not available in MySQL.
- It also introduces features like improved MEMORY storage engine, configurable fast index creation, and per-column compression for VARCHAR/BLOB and JSON, which are not present in MySQL.

**Security Features:**
- Both MySQL and Percona Server for MySQL support features like SQL roles, SHA-2 based password hashing, and password rotation policy. However, Percona Server provides additional security features such as the ability to enforce encryption and an audit logging plugin, which are not available in MySQL.

**Operational Improvements:**
- Percona Server introduces operational improvements such as atomic DDL, transactional data dictionary, instant DDL, and SET PERSIST, which are not present in MySQL. It also offers features like backup locks, invisible indexes, and the ability to kill idle transactions, enhancing operational efficiency.

**Instrumentation and Troubleshooting:**
- Percona Server extends MySQL's capabilities with more extensive instrumentation and troubleshooting features, including INFORMATION_SCHEMA tables, global performance and status counters, optimizer histograms, and enhanced SHOW INNODB ENGINE STATUS.

**Compatibility and Open Source:**
- Both MySQL and Percona Server for MySQL are open-source and compatible with each other, making it relatively easy to switch between them.

**Considerations:**
- While Percona Server offers several enhancements over MySQL, it's important to note that some of its features are not available in MySQL and vice versa. The choice between the two should be based on the specific requirements of your project, including performance needs, scalability, security, and operational efficiency.
- Percona Server's additional features and enhancements may require additional configuration and tuning to fully leverage their benefits.

In summary, if your project requires enhanced performance, scalability, and operational efficiency, Percona Server for MySQL could be a better choice than MySQL. However, if you're looking for a more straightforward, widely-used solution, MySQL might be more suitable.


---


For faster alternatives to MySQL, several options are available, each with its own set of advantages and use cases. Here's a summary of some of the most notable alternatives:

1. **PostgreSQL**: Known for its high performance and ability to make predefined fast queries, PostgreSQL is a powerful, open-source object-relational database system. It's available on Windows, Linux, and macOS, making it a versatile choice. PostgreSQL is particularly noted for its robust feature set, including support for complex queries and a wide range of data types [1].

2. **Cassandra**: This is a highly scalable, high-performance distributed database designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure. Cassandra is particularly well-suited for applications that can't afford to lose data, even in the event of a machine failure. It's also familiar to developers used to SQL, making the transition easier [1].

3. **MongoDB**: A popular NoSQL database, MongoDB is known for its rapid writing and lightning-fast reading capabilities. It's particularly well-suited for handling large amounts of unstructured data due to its master and slave replication capabilities. MongoDB is an open-source database that offers flexibility in querying and data storage, making it a good choice for applications that require high write loads [2].

4. **MariaDB**: MariaDB is a fork of MySQL and is designed to maintain high compatibility with MySQL while also offering new features and performance improvements. It supports a variety of storage engines and is known for its operational agility, full SQL compliance, and ACID compliance. MariaDB is particularly beneficial for analytical, transactional, or hybrid use cases and is used by many major corporations [3].

When choosing an alternative to MySQL, consider the specific requirements of your project, such as the need for scalability, the type of data you're working with (structured vs. unstructured), and the performance characteristics you require (read vs. write performance). Each of these alternatives has its strengths and may be more suitable for certain types of applications or use cases than others.

Dve leti nazaj sem ti napisal, da je brezveze, da implementiras svojo buggy implementacijo search engina in da uporabi ze narejen produkt. In evo nas danes.

Dobro jutro.

Evo, fantje in dekline, zadevo sem rešil z typesense, krho hvala ti za link. Software zgleda top of the top.

Sem pa naletel na en izziv. Za input query imam string: "kaviar kreuzung".

Če search parametre nastavim tako:

search_parameters = {
    "q": "kaviar kreuzung",
    "query_by": "searchname",
    "sort_by": "_text_match:desc,searchname_length:asc"
};

Dobim samo en rezultat, ki ima score: 1157451471441100921

Če pa search parmaterte nastavim takole (dodan filter_by):

search_parameters = {
    "q": "kaviar kreuzung",
    "query_by": "searchname",
    "sort_by": "_text_match:desc,searchname_length:asc",
    "filter_by": "country:us"
};

Pa dobim 14 rezultatov (gre za "caviar" rezultate), od katerih je najvišji score 578730054645710969.

Se pravi:
1157451471441100921
578730054645710969


V SQL-u sem imel preprost sistem: prebral sem max score, ga pomnožil z 0.6, ter vse, ki so imeli manjši score od te vrednosti, sem pred output-om odrezal.

Tu bi lahko naredil podobno. Prvo query brez filtra, preberem score, ga pomnožim z 0.6, ter naredim drug query kjer uporabim filter, ter pri branju preverjam, če score dosega 60% posto od max brez filtra.

Well.... that's how it could be done.

Software pa dela kot šus. Čudno, da Debian nima tega vključenega v njihovih apt source serverjih, isto da nimajo python3-typesense kar pod apt-get install...

Neke malenkosti še morem naredit. Eno je zgenerirat key za search only funkcijonalnost. Drugo je backup database-a.

Ter še ena ne najbolj malenkost. Po restartu serverja (service typesense-server restart) traja dve tri minute, da server dejansko začne vračat podatke. V vmesnem času pa client po kakšnih ~6 sekundah dobi error:

typesense.exceptions.ServiceUnavailable: [Errno 503] Not Ready or Lagging

Zakaj v začetnem času (2~3 minute), ko service že laufa, vrača, da še ni ready, nea vem.

Bi pa rad za client nastavil nižji timeout. Mogoče 1.5 sekunde.

Postavil sem par tehničnih vprašanj. Kje je težava?

Poleg tega, da si neizobražena furja si tudi primitivec, vidim.
tvoje besede me ne užalijo, če slučajno misliš drugače.
Če bi jih upal povedati v obraz in jaz ne bi naredil nič, potem bi bilo pa drugače.

Tako ti pa svetujem samo eno: malo manj trolanja po forumu in bo za vse bolje.
Pa lep dan še naprej!