MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Tom Cunningham Date:December 21 2005 2:45am
Subject:merge table: speed benefits?
View as plain text  
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?

merge table: speed benefits?Tom Cunningham21 Dec
  • Re: merge table: speed benefits?Gleb Paharenko21 Dec
    • Re: merge table: speed benefits?Tom Cunningham21 Dec
  • Re: merge table: speed benefits?beacker24 Dec
    • Re: merge table: speed benefits?Tom Cunningham25 Dec