Database expert to advise on large MySQL database

We have a large MySQL database currently with about 3 million records and 30 GB in size. The purpose is to search quickly the content of many text-snippets of about 10,000 bytes each stored as the "mediumtext" type. A search currently takes about 10-30 seconds for a search query. Currently, it is very slow to search, yet we expect the database will grow to 100 million records with about 800 GB in size. The account is currently on a VPS, but will be moved to dedicated soon. Your job is to improve searching speed, either by using indexes, partitioning, or installing a DB other than MySQL, or any other method. If you change the database from MySQL, you must show how to add new records and search existing records in either PHP or Perl. **Attached is the current structure of the MySQL db.** We will give the winning bidder non-root **ssh/cpanel/myphpadmin** access to the account to work on. If root access is needed, TeamViewer will be used.