Hello, Anyone knows of any database that supports FAST fulltext search. I have around 5M values in the database. They vary from 10chars to 200chars. If anyone has experience with this, please let me know. The table is fully optimized..even separated in sub-tables, letter by letter, but it seems to crack out now. Thank you!!
LIKE is always a linear search in any database. If you use =, not LIKE, and you have your tables indexed properly, that's as fast as that particular database software can go. If you use LIKE, it's a linear search (the database searches the entire table looking for matches), so it's going to crawl. RDB7 uses indexing for BEGINS WITH and ENDS WITH. (But RDB7 isn't cheap like MS SQL Server.)
I presume that you have already added FULL TEXT indexes on column(s) on which search is made. 5M does not seem much of data, but again it depends on row length, type of data being stored, and underlying hardware to determine over all performance. Moreover, queries and indexes should also be checked properly. If above all is proper, then may be you can try external search engine like Sphinx.