Eli <[EMAIL PROTECTED]> wrote on 09/14/2005 10:13:57 AM: > Hi, > > Is it possible, or probably will be possible, to define indexes spanned > on more than one table? > I mean that if I have column col_a from table tbl_A, and col_b from > table tbl_B.. Can I define a KEY with both those columns? Where will the
> index be saved? > > -thanks. > I think you hit the nail on the head with your last question. Which table should hold an index like that, if it were possible to create it. Should both, should one, or should neither (so that it becomes a sort of "global" (server-wide) index).? Indexes have always been table-specific. In the case of certain types of VIEWs being equivalent to table-based information, MSSQL allows you to create indexes on the VIEWs. Transitively, an index on a VIEW that spans several tables would effectively index the columns from the tables that the VIEW gets its data from. Since VIEWs are new to MySQL v5.0+ they are not ready to be INDEXed in the same way that tables are. In addition to VIEWs you can create MERGE tables that span several identically organized MyISAM tables. You can index MERGE tables which, also by transitivity, effectively indexes columns from the underlying tables. However, due to the restriction that MERGE tables must be defined identically to each other, this probably will not meet the intent of your original question. In both cases, we had to define a "super-table" that spanned the tables so that the spanning index could be applied to it and not the tables themselves. Will it be possible to define "global" indexes in the future? I wouldn't hold my breath. Those raise permissions issues and storage issues and query processing overhead that most of the current vendors probably are not ready to take on any time soon. Just my $.02, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
