Playing with merge tables in MySQL, I discovered a new tidbit that we didn’t cover in Performance Tuning Class.? If you are interested in this post, you probably know that a MERGE table allows you to address many identical subtables underneath it without having to write sql for each subtable.? Well, I discovered that not only do the subtables need to be indexed, but you have to apply those indexes to the merge table to.? Since it simply acts as an interface to the subtables, I assumed the indexes on the subtables, would handle stuff, but these indexes must be in the MERGE table too or it will simply do a full table scan.? That will slow you down considerably every time!
Posts Tagged ‘MySQL’
If you have read my previous two posts, you know that I was experiencing some anomalies with MySQL MERGE tables.? Well, since the original discovery of the anomaly, we dropped and recreated the schema and now it’s gone.? It functions as I thought it should have in the first place, ie. each subtable is directly queriable and updatable.? The only thing I can think of is we screwed up somethng in the original create MERGE statement that was weird, although I can imagine what it is.
After more experimentation, I found that you can, in fact, query one of the subtables in a MyISAM MERGE table but not a count.? I think this is even more odd.? I can get at the data, but I can’t find out how much there is.
If you are a geek like me and are using MySQL (The greatest database going), here is an interesting tidbit I discovered. If you create a MYISAM MERGE table merging several identical subtables, you can still insert data directly into the subtables, but you cannot select data directly out of them. For that, you must now go through the merge table. Interesting.