I have a database that will house an etimated 100,000 results. Before, I was planning on breaking up the results into smaller table (about 10 tables to house all of the results.) However, I have run across several problems and I really need all these results to be in the same table. If I have a search page that searches all 100,000 results, is there a significant time issue when it comes to time? I don't want someone's search results to take several minutes just to look something up.

If you are trying to search within text type fields using a similar approach:

SELECT * FROM table WHERE field LIKE '%keyword%'

Then you will have serious result time in a large database table. However, MySQL provides a way for use of Fulltext indexing.

Here is a good tutorial / article:
http://devzone.zend.com/node/view/id/1304

Thanks, that's what I was looking for. Also, what about if I specifically tell the query what to look for.

mysql_query("SELECT * FROM table WHERE name = 'Bill'");

Will there still be a significant response time?

Originally Posted by choskins102

Thanks, that's what I was looking for. Also, what about if I specifically tell the query what to look for.

mysql_query("SELECT * FROM table WHERE name = 'Bill'");

Will there still be a significant response time?

I don't think so, but fulltext searching will allow you to return a result set with similar keyword(s). Your example above will only return exact match (case-sensitive) only which is highly rare.

Fulltext is very much the way to go.

SELECT * FROM table WHERE MATCH(name) AGAINST('Bill')

If you know what you are looking for then an index is appropriate. If it's for partial matches, do what mgraphic has said. If it's for exact, case-insensitive matches, then do an index on the first few characters (try to make it so that the index cardinality is high) and then do a LIKE match on it.