How to setup large database access/cache on LAMP environment? My application is getting very slow I guess because of the ever growing database (30+ tables some with 50,000+ rows). 

Using CodeIgniter's model tier to call MySQL database, I optimized queries &amp; views and what not but to no avail. My limited DBA skills took me so far, but I need to step up my game and learn how to do it on a larger scale.

I investigated a little deeper and found a possible direction of caching (?) in memcache, sphinx, HandlerSocket, redis. I also thought it might be logical to use a middle tier (compiled C++ ? Java?) between the DB and PHP. Also I am looking into moving my setup to the cloud (beyond regular VPS) so I will have better control over memory and setup.

Can someone please tell me if I am barking up the wrong tree here? I will read and investigate according to your answers and hand it over to a DBA pro, but I would like to understand the methodology and services I should be looking at.

Thanks.
50K rows tables are rather small. Before throwing more hardware at it, I'd recommend checking/tuning the following:


my.cnf setting: make sure you have good buffers on InnoDB buffer, Key Cache, Query cache, etc. If website is calling same queries over and over, query cache could help.
Check slow query log and optimize worst queries. It'd say this would result in best result for your efforts.


You may need to normalize some tables, but above two are least invasive options that could take your DB performance to next level.