MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:December 21 2005 11:19am
Subject:Re: merge table: speed benefits?
View as plain text  
Hello.

> (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.

IMHO, you should get speed benefits in concurrent user environment, when
several queries are running at once. In the same time, they might be
reading data from the different disks, and it is much faster than
reading from one disk.

> (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.

Manual says that you'll get more speed when you do your searches in
one of the underlying tables directly if you know that the data is
stored there. If you have enough memory so the whole index is stored
in key_buffer you shouldn't get big differences in speed between MERGE
and "monster" tables, but I'm not sure about the case when you have
limited amount of memory, and MySQL often performs key reads, it seems
from the manual that MERGE should be slower.

> (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?

As far as I know MySQL can't do this. BTW, partitioning feature which
is present in MySQL 5.1 is that you're looking for.




Tom Cunningham wrote:
> 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.
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
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