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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
