Hi all.
I'm working on splitting a 5G myisam fact table into separate parts,
and putting a merge table on top.
his will definitely help with table management, but I am hoping that a
merge table might help me with query speed:
(a) The docs say that spreading the underlying tables across different
disks can make queries faster. I don't quite understand how this will
work in a normal query: if I do a SUM(amount) over the entire table,
will it be quicker if the table is spread across different disks? I
don't see how mysql can avoid doing this sequentially.
(b) I also thought that if I do a series of queries, via the merge
table, which all go to a single underlying table, then that would be
quicker than the same queries to the original monster table: because
the index and data caches would just be cacheing for a single table.
<i>is this true?</i>. On reflection I thought that the index and data
caches only cache *parts* of tables, not entire tables, so it will
make no difference.
(c) Finally: in theory the optimiser could use two indexes: use index
A to decide which tables to look at, then use index B within those
tables to find the rows that meet another condition. This would be an
advantage over a non-merged table. But I don't think Mysql can do
this, can it?
So should I expect any speed benefits to splitting my fact table?
Tom.
