I tried converting the main search table to InnoDB but that just made everything horrendously slow - I also heard that it limits to 200 inserts per second whereas with myIsam we're getting around 1k - 1.5k per second.
Could the slow down be due to not converting the linked tables to innoDB? Or just lack of caching?
InnoDB will always be slower then myisam, because it's extra functionalitys create extra overhead.
a heap should be a lott faster but i realy have no idea if it would be a good idea to load a 600MB table into memory. if i'm not mistaken, you also can't text or blob field in a heap. so if you're searching on those ....
Maybe you can just load the columns you actually search on into memory?
- make sure that your colmumntypes are as small as possible --> saves memory on selects
- try to avoid data-manipulations during the day (do your inserts/deletes/updates during the day)
- keep in mind that indexes slow down your inserts --> might be required to drop the index before large number of inserts, and then recreate is again
- keep your table as small as possible --> delete or archive older records
- measure how long the selects actually take --> it's a known fact that most users have the impression that applications are getting slower and slower, while this is actually not happening at all.