Monty helped me with this, and this is my
understanding.
Regardless of what order you generate the keys,
MySQL puts the primary key first, then the UNIQUE
keys, then the others.
When you add a new UNIQUE key, it puts it after
the primary key but before the other existing
UNIQUE keys.
In your case you have only non-UNIQUE keys in the
merge table so you just need to add them in the
same order as is in the dataN tables and MySQL
will maintain the order:
ALTER TABLE all_records DROP INDEX id_kex, DROP
INDEX catalog_key;
ALTER TABLE all_records ADD INDEX id_key (id_key),
ADD INDEX catalog_key (catalog_key);
--Bill
Johnny Withers wrote:
> Well, I don't know if you can specify what order
> to put these in,
> but mine just happen to not be in the same
> order:
>
> mysql> show index from all_records;
> +------------+-------------+-------
> ------+-------------+-----------+---
> ----------+----------+--------+
> | Non_unique | Key_name | Seq_in_index |
> Column_name | Collation |
> Cardinality | Sub_part | Packed |
> +------------+-------------+-----
> --------+-------------+-----------+---
> ----------+----------+--------+
> | 1 | catalog_key | 1 |
> catalog | A |
> NULL | NULL | NULL |
> | 1 | id_key | 1 |
> id | A |
> NULL | NULL | NULL |
> +------------+------------
> +--------------+-------------+-----------+---
> ----------+----------+--------+
> 2 rows in set (0.00 sec)
>
> mysql> show index from data1[,2,3,4,5];
> +------------+-------------+-----------
> --+-------------+-----------+---
> ----------+----------+--------+
> | Non_unique | Key_name | Seq_in_index |
> Column_name | Collation |
> Cardinality | Sub_part | Packed |
> +------------+-------------+-----
> --------+-------------+-----------+---
> ----------+----------+--------+
> | 0 | PRIMARY | 1 |
> id | A |
> 93 | NULL | NULL |
> | 1 | catalog_key | 1 |
> catalog | A |
> NULL | NULL | NULL |
> +------------+------------
> +--------------+-------------+-----------+---
> ----------+----------+--------+
>
> As you can see, the data1,2,3,4,5 tables that
> make up the 'all_records'
> table
> have a primary key defined on ID. I was unable
> to define the ID field
> in
> my merged table as primary because it has to be
> non_unique. This may
> have
> caused the problem.
>
> Maybe making the id columns in data1,2,3,4,5 a
> normal key instead of
> PIMARY will solve the problem.
>
> (Filter : MySQL,database,SQL,etc)
>
> ---------------------
> Johnny Withers
> [EMAIL PROTECTED]
> p. 601.853.0211
> c. 601.209.4985
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On
> Behalf Of Adams, Bill TQO
> Sent: Thursday, August 30, 2001 1:11 PM
> To: Sergei Golubchik
> Cc: Johnny Withers; Mysql-List
> Subject: Re: MERGE TABLES
>
> I have been playing around with merge tables.
> You MUST have the columns
> and
> indexes in the same order.
>
> Eg. Do
> SHOW INDEX FROM real_table;
> SHOW INDEX FROM merge_table;
>
> If the Column_name order is different you will
> get either no records or
> a
> bunch of null records when you select on a
> column that is indexed.
>
> b.
--
Bill Adams
TriQuint Semiconductor
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
