MySQL Caching / PHP Input Suggestion

[mattkenefick]

I'm making a Search Suggestor that every time someone searches something, it adds it to a database. If its already in there, it increments the value by 1. I'm building it in Flash using AMFPHP to connect to the database so its normal PHP Queries and results etc..

 

How should I go about handling the database queries if there were say 5,000+ people using it at once?

 

I'm going to have it query per second rather than per keystroke (only if the input has changed from one second to the next so its not querying the same thing over and over).

 

Is there a more efficient way to do this?

[btherl]

I would recommend having a static database for queries that is rebuilt regularly (daily or weekly).  That should be sufficient for your particular application.  Then your query database can be optimized for searching, and your query logging database can be optimized for fast inserts.  5000+ simultaneous users is a lot btw

 

If you have a high volume of inserts/updates, one solution is to do inserts only, then aggregate the data later.  That allows you to write the data sequentially (assumign nothing else is using that disk), which is a big win over having to do random access to check if a particular query exists already.