| MERGE and indexes [message #1875] |
Fri, 14 September 2007 18:11 |
 bluem
Messages: 15
Registered: November 2006 |
Junior Member |
|
|
I'm trying to decide how big to make underlying tables in a "merge" table (engine=merge). I'm dealing with millions of rows and the underlying table sizes could be, say, 50MB to 2GB plus. Also, I may actually need to do some huge joins on -two- different "merge" tables. What are some of my optimization options?
In MySQL manual I read:
"When you read a key, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the MERGE storage engine needs to search the read buffers to find the next key. Only when one key buffer is used up does the storage engine need to read the next key block. This makes MERGE keys much slower on eq_ref searches, but not much slower on ref searches."
If I do a SELECT using a single indexed column on on merge table, it's actually very fast. If I try to do a JOIN on two "merge" tables, it's much slower, even if the column(s) are indexed. Is this what the MySQL manual quote above is referring to?
My options might be something like this:
100 100MB underlying tables for the merge table
or
5 2GB underlying tables for the merge table
If you have some advice on the various considerations involved, please let me know.
|
|
|
Members
Search
Help
Register
Login
Home
