Huge relational database

MySQL & MariaDB Other MySQL® Questions
1

Hi all

I am working on a database and application that is a search engine of sorts, I cant reveal exactly what it does or who its for due to a NDA.

The database is continually growing and is up to 8 million records, for performance we have setup two servers. One has InnoDB tables and is the master server. The other has the same tables, but they are MyISAM, and it is slaved to the master.

This was done so the back end had good write performance, and the front end (user interface) has good read performance. This works quite well.

The table that has over 8 million records has a varchar(512) field in it that needs to be free text searched. Currently I maintain a seperate table that has distinct words in it, and a relationship table of word → record. This way I can search for records that contain specific words much faster.

Is this the best way to do this? Would there be a better way? The database is growing daily since it is being populated by a crawling engine, so we expect it to grow and grow over time.

2

I just discovered fulltext searching… my concern is, would this get too slow over size/time?

3

Use a real search engine and you can probably switch back to a single server.

4

Fulltext indexes will get much slower as the data doesn’t fit into memory anymore. Sphinx is how most of our customers solve that problem. I agree with gmouse.