I have a medium-sized database with about 100M records in about 300 tables. The largest tables have at more 9M records. To help speed up some of the queries, which need to display real-time results, I have the system creating MEMORY tables (TEMPORARY tables didn’t work for me). I have about 7K MEMORY tables, and I was wondering if putting these temporary tables into a separate database schema would make a performance difference.
Currently I’m performing an average of 125 queries per second. The entire database fits into memory (INNODB buffer pool) and seems to perform quite well overall.
My concern is that when I have to start using replication I may encounter issues with these temporary tables. MySQL Cluster is not available as an option on Windows yet, and since that’s all I know how to administer I have to keep using it.
So what do we know about the performance of tables being accessed across databases and the performance impact of this?
Thanks in advance,
Michael Myles