The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets executed 7,00,000 times in a single day with updated data. A query is already optimized and indexed. Table approximately has 300,00,000 rows. Is there any way to reduce the query time? The table works on the InnoDB engine.
I was planning for few methods please correct me if I am wrong
- Memcache the rows using PHP
- Use replication Master/Slave architecture in slave have MyISAM (Doubt about the engine in this architecture)
- Use MySQL caching mechanism
query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576
- Can both (a) and (b) perform together? Will it improve the performance for such huge data?
Is there some other technique for improving query execution time?