Mysql search table optimization for a game website

The obvious problem with your _searches table is that there is no index on the 'term' column by which you are searching. So you should start by adding an index (perhaps unique also, I will explain below why)

ie:

CREATE [UNIQUE] INDEX IX_Term on _searches(term);

This will speed up very much your select query. Also you could improve the if statement below by using INSERT DELAYED (http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html) and ON DUPLICATE KEY UPDATE (http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html). For this to work you need a UNIQUE INDEX on term. So the query could be:

INSERT DELAYED INTO _searches (term, viewcount,date) VALUES ('$term', 1, '$date') ON DUPLICATE KEY UPDATE viewcount = viewcount + 1;

Please note that $term and $date should be escaped to avoid SQL injections

What the above query does is to insert a new row in the table but if the term already exists (restricted by the UNIQUE INDEX) instead of inserting it will update the existing row incrementing the viewcount by 1.

Also note that UPDATES can lock your table up restricting future INSERTS and that could easily cause a deadlock on your database if your _searches table grows too big.

1. you can add "on duplicate key update" to your insert query to _search

instead of

$c = mysql_query("SELECT id FROM _searches WHERE term='$term'");if (mysql_num_rows($c) == 1) {    mysql_query("update _searches set viewcount=viewcount+1,date='$date' WHERE term='$term'");} else {    mysql_query("insert into _searches (term,viewcount,date) values ('$term',1,'$date')");}

you can write

mysql_query("insert into _searches (term,viewcount,date) values ('$term',1,'$date') on duplicate key update viewcount=viewcount+1, date=$date")

also i see that, you don't have a index at "term" field... selecting from non-indexed field can cause this problem too..

and never forget that, full text search is always slow... can you install a full text search engine like sphinx (http://sphinxsearch.com/) or solr (http://lucene.apache.org/solr/) and also caching to memory with memcache or apc will definitly help you.