From: Ryan N. <nov...@um...> - 2008-08-18 19:41:20
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Technik wrote: >> I really sort of feel like I have too many indexes now, but... who >> knows. Anyone know if MySQL will allow you to define two of the same >> index? > > Why? Have you already changed something on the indices on the tables? I had attempted to fix this problem some time back and took some recommendations. Apparently they didn't help. Neither the wiki or the help I'd received at that time appeared to make much of a difference. > Yes, it's possible as long as the index names are different. Sorry, but > I don't have your mysql output anymore. But each line in your posted > output represents a column in the index. So for each column in the index > exist one record. Why would one want to indexes on the same set of columns? Or is this not what's happening? > An example based on my previous post: > > First record: Primary index (column index:1) > Second record: JobId on the column JobId (column index:1) > Third record: JobId_2 on the column JobId (column index:1) > Fourth record: JobId_2 on the column PathId (column index:2) > Fifth record: JobId_2 on the column FilenameId (column index:3) > Sixth record: PathId on the column PathId (column index:1) > Seventh record: FilenameId on the column FilenameId (column index:1) And that is my question. Let's say the second and third record. Why does one want both of those? They appear to be both indexing the same column, right? I have: mysql> show index from File; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | File | 0 | PRIMARY | 1 | FileId | A | 3569481 | NULL | NULL | | BTREE | | | File | 1 | JobId | 1 | JobId | A | 129 | NULL | NULL | | BTREE | | | File | 1 | JobId | 2 | PathId | A | 274575 | NULL | NULL | | BTREE | | | File | 1 | JobId | 3 | FilenameId | A | 3569481 | NULL | NULL | | BTREE | | | File | 1 | file_jobid_idx | 1 | JobId | A | 129 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 1 | JobId | A | 129 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 2 | FilenameId | A | 1784740 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 3 | PathId | A | 3569481 | NULL | NULL | | BTREE | | | File | 1 | PathId | 1 | PathId | A | 127481 | NULL | NULL | | BTREE | | | File | 1 | FilenameId | 1 | FilenameId | A | 446185 | NULL | NULL | | BTREE | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > At the end there are seven records for five indices. I have 9. I just wanted to make sure I'm not doing anything redundant here. > Hopes this helps. Thanks for looking! - -- ---- _ _ _ _ ___ _ _ _ |Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Systems Programmer II |$&| |__| | | |__/ | \| _| |nov...@um... - 973/972.0922 (2-0922) \__/ Univ. of Med. and Dent.|IST/AST - NJMS Medical Science Bldg - C630 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIqdBpmb+gadEcsb4RAgYGAJkBlf555EogRgW/yqtegpHzIBb8ZwCgwHXz vL5BG3xMiksa14rn+nLHzVY= =tbVM -----END PGP SIGNATURE----- |