For big sites, say tens of thousands of nodes, the Drupal core search module is not suitable any longer. You could tweak it to handle hundreds of thousands of nodes, but there is a limit. To handle high search volumes, one should look to external third party search solutions. The most popular -open source- tool is Apache Solr. The Solr search can be integrated with Drupal via theApache Solr Intergration project.
Acquia also offers a hosted solr solutions, which prices at 30-50$ per month.
Oh, while you're at it: disable core search indexing. The trick is to clear the index, and then disable it via the Apache Solr setting. Core indexing will not take place, the tables now remain empty. And that is the goal: to reduce MySQL load.
- Enhanced search features, e.g. faceted search
- Faster search handling
- Reduced MySQL load, freeing up more resources for Drupal