GID redirect tables have no indexes
Closed this issue · 6 comments
Currently none of the *_gid_redirect tables have indexes.
Although these tables are relatively small, I think an index should be created at least for the gid field (and possibly on th newid field if we want to do reverse lookups, i.e. get which GIDs have been associated with a particular entity).
This means a primary key is supposed to be created on that, perhaps it fails?
Are you using non-NGS? If so it's likely that version of CreatePrimaryKeys.sql does not have that line 10. However the keys get downloaded and processed by http://github.com/chancemedia/mbzdb/blob/master/settings.pl#L71
Strange.... I manually created them with no error, but my CreatePrimaryKeys.sql is supposed to have created them. Other tables do have all the supposed PKs and indexes.
By the way, I'll check again the next time I re-create the MB database....
I'm posting the result of the last time I created the DB:
Ready to proceed? (it may take over 24 hours for this script to fully complete) (y/n): y
Downloading schema... Done
Table annotation
Table artist
Table artist_alias
Table artist_annotation
Table artist_meta
Table artist_tag
Table artist_credit
Table artist_credit_name
Table artist_gid_redirect
Table artist_name
Table artist_type
Table cdtoc
Table clientversion
Table country
Table currentstat
Table editor
Table editor_preference
Table editor_subscribe_artist
Table editor_subscribe_label
Table editor_subscribe_editor
Table gender
Table isrc
Table historicalstat
Table l_artist_artist
Table l_artist_label
Table l_artist_recording
Table l_artist_release
Table l_artist_release_group
Table l_artist_url
Table l_artist_work
Table l_label_label
Table l_label_recording
Table l_label_release
Table l_label_release_group
Table l_label_url
Table l_label_work
Table l_recording_recording
Table l_recording_release
Table l_recording_release_group
Table l_recording_url
Table l_recording_work
Table l_release_release
Table l_release_release_group
Table l_release_url
Table l_release_work
Table l_release_group_release_group
Table l_release_group_url
Table l_release_group_work
Table l_url_url
Table l_url_work
Table l_work_work
Table label
Table label_alias
Table label_annotation
Table label_meta
Table label_gid_redirect
Table label_name
Table label_tag
Table label_type
Table language
Table link
Table link_attribute
Table link_attribute_type
Table link_type
Table link_type_attribute_type
Table editor_collection
Table editor_collection_release
Table editor_watch_preferences
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERVAL NOT NULL DEFAULT '1 week'' at line 1 at backend/mysql.pl line 360, line 1036.
Table editor_watch_artist
Table editor_watch_release_group_type
Table editor_watch_release_status
Table medium
Table medium_cdtoc
Table medium_format
Table puid
Table replication_control
Table recording
Table recording_annotation
Table recording_meta
Table recording_gid_redirect
Table recording_puid
Table recording_tag
Table release
Table release_annotation
Table release_gid_redirect
Table release_meta
Table release_coverart
Table release_label
Table release_packaging
Table release_status
Table release_tag
Table release_group
Table release_group_annotation
Table release_group_gid_redirect
Table release_group_meta
Table release_group_tag
Table release_group_type
Table release_name
Table script
Table script_language
Table tag
Table tag_relation
Table track
Table track_name
Table tracklist
Table tracklist_index
Table url
Table url_gid_redirect
Table work
Table work_alias
Table work_annotation
Table work_gid_redirect
Table work_meta
Table work_name
Table work_tag
Table work_type
Table ATE array_cat_agg(anyarray)
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= array_cat' at line 1 at backend/mysql.pl line 360, line 37.
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= anyarray' at line 1 at backend/mysql.pl line 360, line 37.
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 0' at line 1 at backend/mysql.pl line 360, line 37.
DBD::mysql::db do failed: You can't delete all columns with ALTER TABLE; use DROP TABLE instead at backend/mysql.pl line 360, line 37.
Table dbmirror_Pending
Table dbmirror_PendingData
Logging into MusicBrainz FTP...
The latest is mbdump is '20101229'
Sat Jan 8 15:34:15 2011: Downloading mbdump-derived.tar.bz2... File already downloaded
Sat Jan 8 15:34:15 2011: Downloading mbdump-stats.tar.bz2... File already downloaded
Sat Jan 8 15:34:15 2011: Downloading mbdump.tar.bz2... File already downloaded
Sat Jan 8 15:34:15 2011: Uncompressing mbdump-derived.tar.bz2... Done
Sat Jan 8 15:34:56 2011: Uncompressing mbdump-stats.tar.bz2... Done
Sat Jan 8 15:34:57 2011: Uncompressing mbdump.tar.bz2... Done
Sat Jan 8 15:41:41 2011: Loading data into 'annotation' (1 of 101)... Done ( 0m 18s)
Sat Jan 8 15:41:59 2011: Loading data into 'artist' (2 of 101)... Done ( 0m 20s)
Sat Jan 8 15:42:19 2011: Loading data into 'artist_alias' (3 of 101)... Done ( 0m 2s)
Sat Jan 8 15:42:21 2011: Loading data into 'artist_annotation' (4 of 101)... Done ( 0m 1s)
Sat Jan 8 15:42:22 2011: Loading data into 'artist_credit' (5 of 101)... Done ( 0m 10s)
Sat Jan 8 15:42:32 2011: Loading data into 'artist_credit_name' (6 of 101)... Done ( 0m 8s)
Sat Jan 8 15:42:40 2011: Loading data into 'artist_gid_redirect' (7 of 101)... Done ( 0m 1s)
Sat Jan 8 15:42:41 2011: Loading data into 'artist_meta' (8 of 101)... Done ( 0m 9s)
Sat Jan 8 15:42:50 2011: Loading data into 'artist_name' (9 of 101)... Done ( 0m 19s)
Sat Jan 8 15:43:09 2011: Loading data into 'artist_tag' (10 of 101)... Done ( 0m 2s)
Sat Jan 8 15:43:11 2011: Loading data into 'artist_type' (11 of 101)... Done ( 0m 0s)
Sat Jan 8 15:43:11 2011: Loading data into 'cdtoc' (12 of 101)... Done ( 0m 21s)
Sat Jan 8 15:43:32 2011: Loading data into 'clientversion' (13 of 101)... Done ( 0m 0s)
Sat Jan 8 15:43:32 2011: Loading data into 'country' (14 of 101)... Done ( 0m 0s)
Sat Jan 8 15:43:32 2011: Loading data into 'currentstat' (15 of 101)... Done ( 0m 0s)
Sat Jan 8 15:43:32 2011: Loading data into 'gender' (16 of 101)... Done ( 0m 0s)
Sat Jan 8 15:43:32 2011: Loading data into 'historicalstat' (17 of 101)... Done ( 0m 5s)
Sat Jan 8 15:43:37 2011: Loading data into 'isrc' (18 of 101)... Done ( 0m 4s)
Sat Jan 8 15:43:41 2011: Loading data into 'l_artist_artist' (19 of 101)... Done ( 0m 3s)
Sat Jan 8 15:43:44 2011: Loading data into 'l_artist_label' (20 of 101)... Done ( 0m 0s)
Sat Jan 8 15:43:44 2011: Loading data into 'l_artist_recording' (21 of 101)... Done ( 0m 18s)
Sat Jan 8 15:44:02 2011: Loading data into 'l_artist_release' (22 of 101)... Done ( 0m 6s)
Sat Jan 8 15:44:08 2011: Loading data into 'l_artist_release_group' (23 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:08 2011: Loading data into 'l_artist_url' (24 of 101)... Done ( 0m 6s)
Sat Jan 8 15:44:14 2011: Loading data into 'l_artist_work' (25 of 101)... Done ( 0m 10s)
Sat Jan 8 15:44:24 2011: Loading data into 'l_label_label' (26 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:24 2011: Loading data into 'l_label_recording' (27 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:24 2011: Loading data into 'l_label_release' (28 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:24 2011: Loading data into 'l_label_release_group' (29 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:24 2011: Loading data into 'l_label_url' (30 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:25 2011: Loading data into 'l_label_work' (31 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:25 2011: Loading data into 'l_recording_recording' (32 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:26 2011: Loading data into 'l_recording_release' (33 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:26 2011: Loading data into 'l_recording_release_group' (34 of 101)...Done ( 0m 0s)
Sat Jan 8 15:44:26 2011: Loading data into 'l_recording_url' (35 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:26 2011: Loading data into 'l_recording_work' (36 of 101)... Done ( 0m 6s)
Sat Jan 8 15:44:32 2011: Loading data into 'l_release_group_release_group' (37 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:32 2011: Loading data into 'l_release_group_url' (38 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:33 2011: Loading data into 'l_release_group_work' (39 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:33 2011: Loading data into 'l_release_release' (40 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:34 2011: Loading data into 'l_release_release_group' (41 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:34 2011: Loading data into 'l_release_url' (42 of 101)... Done ( 0m 14s)
Sat Jan 8 15:44:48 2011: Loading data into 'l_release_work' (43 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:48 2011: Loading data into 'l_url_url' (44 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:48 2011: Loading data into 'l_url_work' (45 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:48 2011: Loading data into 'l_work_work' (46 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:48 2011: Loading data into 'label' (47 of 101)... Done ( 0m 2s)
Sat Jan 8 15:44:50 2011: Loading data into 'label_alias' (48 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:50 2011: Loading data into 'label_annotation' (49 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:51 2011: Loading data into 'label_gid_redirect' (50 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:51 2011: Loading data into 'label_meta' (51 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:51 2011: Loading data into 'label_name' (52 of 101)... Done ( 0m 2s)
Sat Jan 8 15:44:53 2011: Loading data into 'label_tag' (53 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:53 2011: Loading data into 'label_type' (54 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:53 2011: Loading data into 'language' (55 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:53 2011: Loading data into 'link' (56 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:54 2011: Loading data into 'link_attribute' (57 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:54 2011: Loading data into 'link_attribute_type' (58 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:54 2011: Loading data into 'link_type' (59 of 101)... Done ( 0m 1s)
Sat Jan 8 15:44:55 2011: Loading data into 'link_type_attribute_type' (60 of 101)... Done ( 0m 0s)
Sat Jan 8 15:44:55 2011: Loading data into 'medium' (61 of 101)... Done ( 0m 24s)
Sat Jan 8 15:45:19 2011: Loading data into 'medium_cdtoc' (62 of 101)... Done ( 0m 11s)
Sat Jan 8 15:45:30 2011: Loading data into 'medium_format' (63 of 101)... Done ( 0m 0s)
Sat Jan 8 15:45:30 2011: Loading data into 'puid' (64 of 101)... Done ( 1m 49s)
Sat Jan 8 15:47:19 2011: Loading data into 'recording' (65 of 101)... Done ( 4m 40s)
Sat Jan 8 15:51:59 2011: Loading data into 'recording_annotation' (66 of 101)... Done ( 0m 0s)
Sat Jan 8 15:51:59 2011: Loading data into 'recording_gid_redirect' (67 of 101)... Done ( 0m 19s)
Sat Jan 8 15:52:18 2011: Loading data into 'recording_meta' (68 of 101)... Done ( 2m 31s)
Sat Jan 8 15:54:49 2011: Loading data into 'recording_puid' (69 of 101)... Done ( 1m 50s)
Sat Jan 8 15:56:39 2011: Loading data into 'recording_tag' (70 of 101)... Done ( 0m 5s)
Sat Jan 8 15:56:44 2011: Loading data into 'release' (71 of 101)... Done ( 0m 35s)
Sat Jan 8 15:57:19 2011: Loading data into 'release_annotation' (72 of 101)... Done ( 0m 3s)
Sat Jan 8 15:57:22 2011: Loading data into 'release_gid_redirect' (73 of 101)... Done ( 0m 2s)
Sat Jan 8 15:57:24 2011: Loading data into 'release_group' (74 of 101)... Done ( 0m 23s)
Sat Jan 8 15:57:47 2011: Loading data into 'release_group_annotation' (75 of 101)... Done ( 0m 0s)
Sat Jan 8 15:57:47 2011: Loading data into 'release_group_gid_redirect' (76 of 101)... Done ( 0m 2s)
Sat Jan 8 15:57:49 2011: Loading data into 'release_group_meta' (77 of 101)... Done ( 0m 14s)
Sat Jan 8 15:58:03 2011: Loading data into 'release_group_tag' (78 of 101)... Done ( 0m 6s)
Sat Jan 8 15:58:09 2011: Loading data into 'release_group_type' (79 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:09 2011: Loading data into 'release_label' (80 of 101)... Done ( 0m 11s)
Sat Jan 8 15:58:20 2011: Loading data into 'release_meta' (81 of 101)... Done ( 0m 21s)
Sat Jan 8 15:58:41 2011: Loading data into 'release_name' (82 of 101)... Done ( 0m 17s)
Sat Jan 8 15:58:58 2011: Loading data into 'release_packaging' (83 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'release_status' (84 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'release_tag' (85 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'replication_control' (86 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'script' (87 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'script_language' (88 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'tag' (89 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'tag_relation' (90 of 101)... Done ( 0m 0s)
Sat Jan 8 15:58:58 2011: Loading data into 'track' (91 of 101)... Done ( 4m 49s)
Sat Jan 8 16:03:47 2011: Loading data into 'track_name' (92 of 101)... Done ( 1m 42s)
Sat Jan 8 16:05:29 2011: Loading data into 'tracklist' (93 of 101)... Done ( 0m 17s)
Sat Jan 8 16:05:46 2011: Loading data into 'url' (94 of 101)... Done ( 0m 31s)
Sat Jan 8 16:06:17 2011: Loading data into 'work' (95 of 101)... Done ( 0m 8s)
Sat Jan 8 16:06:25 2011: Loading data into 'work_annotation' (96 of 101)... Done ( 0m 0s)
Sat Jan 8 16:06:25 2011: Loading data into 'work_gid_redirect' (97 of 101)... Done ( 0m 0s)
Sat Jan 8 16:06:25 2011: Loading data into 'work_meta' (98 of 101)... Done ( 0m 5s)
Sat Jan 8 16:06:30 2011: Loading data into 'work_name' (99 of 101)... Done ( 0m 4s)
Sat Jan 8 16:06:34 2011: Loading data into 'work_tag' (100 of 101)... Done ( 0m 0s)
Sat Jan 8 16:06:34 2011: Loading data into 'work_type' (101 of 101)... Done ( 0m 0s)
Complete (24m 53s)
CREATE UNIQUE INDEX artist_idx_gid ON artist (gid)
CREATE INDEX artist_idx_name ON artist (name)
CREATE INDEX artist_idx_sort_name ON artist (sort_name)
CREATE INDEX artist_idx_ipi_code ON artist (ipi_code)
CREATE INDEX artist_alias_idx_artist ON artist_alias (artist)
CREATE UNIQUE INDEX artist_alias_idx_locale_artist ON artist_alias (artist,locale(32))
CREATE INDEX artist_credit_name_idx_artist ON artist_credit_name (artist)
CREATE UNIQUE INDEX artist_name_idx_name ON artist_name (name(32))
DBD::mysql::db do failed: Duplicate entry 'Grant, Charlie & Woodroffe, Pete & Osman, Iman' for key 'artist_name_idx_name' at src/functions.pl line 96, line 213.
CREATE INDEX artist_tag_idx_tag ON artist_tag (tag)
CREATE INDEX artist_tag_idx_artist ON artist_tag (artist)
CREATE UNIQUE INDEX country_idx_iso_code ON country (iso_code)
CREATE INDEX currentstat_name ON currentstat (name)
CREATE INDEX editor_subscribe_artist_idx_uniq ON editor_subscribe_artist (editor,artist)
CREATE INDEX editor_subscribe_label_idx_uniq ON editor_subscribe_label (editor,label)
CREATE INDEX editor_subscribe_editor_idx_uniq ON editor_subscribe_editor (editor,subscribed_editor)
CREATE INDEX historicalstat_date ON historicalstat (snapshot_date)
CREATE INDEX historicalstat_name_snapshot_date ON historicalstat (name,snapshot_date)
CREATE INDEX isrc_idx_isrc ON isrc (isrc)
CREATE UNIQUE INDEX l_artist_artist_idx_uniq ON l_artist_artist (entity0,entity1,link)
CREATE UNIQUE INDEX l_artist_label_idx_uniq ON l_artist_label (entity0,entity1,link)
CREATE UNIQUE INDEX l_artist_recording_idx_uniq ON l_artist_recording (entity0,entity1,link)
CREATE UNIQUE INDEX l_artist_release_idx_uniq ON l_artist_release (entity0,entity1,link)
CREATE UNIQUE INDEX l_artist_release_group_idx_uniq ON l_artist_release_group (entity0,entity1,link)
CREATE UNIQUE INDEX l_artist_url_idx_uniq ON l_artist_url (entity0,entity1,link)
CREATE UNIQUE INDEX l_artist_work_idx_uniq ON l_artist_work (entity0,entity1,link)
CREATE UNIQUE INDEX l_label_label_idx_uniq ON l_label_label (entity0,entity1,link)
CREATE UNIQUE INDEX l_label_recording_idx_uniq ON l_label_recording (entity0,entity1,link)
CREATE UNIQUE INDEX l_label_release_idx_uniq ON l_label_release (entity0,entity1,link)
CREATE UNIQUE INDEX l_label_release_group_idx_uniq ON l_label_release_group (entity0,entity1,link)
CREATE UNIQUE INDEX l_label_url_idx_uniq ON l_label_url (entity0,entity1,link)
CREATE UNIQUE INDEX l_label_work_idx_uniq ON l_label_work (entity0,entity1,link)
CREATE UNIQUE INDEX l_recording_recording_idx_uniq ON l_recording_recording (entity0,entity1,link)
CREATE UNIQUE INDEX l_recording_release_idx_uniq ON l_recording_release (entity0,entity1,link)
CREATE UNIQUE INDEX l_recording_release_group_idx_uniq ON l_recording_release_group (entity0,entity1,link)
CREATE UNIQUE INDEX l_recording_url_idx_uniq ON l_recording_url (entity0,entity1,link)
CREATE UNIQUE INDEX l_recording_work_idx_uniq ON l_recording_work (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_release_idx_uniq ON l_release_release (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_release_group_idx_uniq ON l_release_release_group (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_url_idx_uniq ON l_release_url (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_work_idx_uniq ON l_release_work (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_group_release_group_idx_uniq ON l_release_group_release_group (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_group_url_idx_uniq ON l_release_group_url (entity0,entity1,link)
CREATE UNIQUE INDEX l_release_group_work_idx_uniq ON l_release_group_work (entity0,entity1,link)
CREATE UNIQUE INDEX l_url_url_idx_uniq ON l_url_url (entity0,entity1,link)
CREATE UNIQUE INDEX l_url_work_idx_uniq ON l_url_work (entity0,entity1,link)
CREATE UNIQUE INDEX l_work_work_idx_uniq ON l_work_work (entity0,entity1,link)
CREATE INDEX l_artist_artist_idx_entity1 ON l_artist_artist (entity1)
CREATE INDEX l_artist_label_idx_entity1 ON l_artist_label (entity1)
CREATE INDEX l_artist_recording_idx_entity1 ON l_artist_recording (entity1)
CREATE INDEX l_artist_release_idx_entity1 ON l_artist_release (entity1)
CREATE INDEX l_artist_release_group_idx_entity1 ON l_artist_release_group (entity1)
CREATE INDEX l_artist_url_idx_entity1 ON l_artist_url (entity1)
CREATE INDEX l_artist_work_idx_entity1 ON l_artist_work (entity1)
CREATE INDEX l_label_label_idx_entity1 ON l_label_label (entity1)
CREATE INDEX l_label_recording_idx_entity1 ON l_label_recording (entity1)
CREATE INDEX l_label_release_idx_entity1 ON l_label_release (entity1)
CREATE INDEX l_label_release_group_idx_entity1 ON l_label_release_group (entity1)
CREATE INDEX l_label_url_idx_entity1 ON l_label_url (entity1)
CREATE INDEX l_label_work_idx_entity1 ON l_label_work (entity1)
CREATE INDEX l_recording_recording_idx_entity1 ON l_recording_recording (entity1)
CREATE INDEX l_recording_release_idx_entity1 ON l_recording_release (entity1)
CREATE INDEX l_recording_release_group_idx_entity1 ON l_recording_release_group (entity1)
CREATE INDEX l_recording_url_idx_entity1 ON l_recording_url (entity1)
CREATE INDEX l_recording_work_idx_entity1 ON l_recording_work (entity1)
CREATE INDEX l_release_release_idx_entity1 ON l_release_release (entity1)
CREATE INDEX l_release_release_group_idx_entity1 ON l_release_release_group (entity1)
CREATE INDEX l_release_url_idx_entity1 ON l_release_url (entity1)
CREATE INDEX l_release_work_idx_entity1 ON l_release_work (entity1)
CREATE INDEX l_release_group_release_group_idx_entity1 ON l_release_group_release_group (entity1)
CREATE INDEX l_release_group_url_idx_entity1 ON l_release_group_url (entity1)
CREATE INDEX l_release_group_work_idx_entity1 ON l_release_group_work (entity1)
CREATE INDEX l_url_url_idx_entity1 ON l_url_url (entity1)
CREATE INDEX l_url_work_idx_entity1 ON l_url_work (entity1)
CREATE INDEX l_work_work_idx_entity1 ON l_work_work (entity1)
CREATE UNIQUE INDEX link_type_idx_gid ON link_type (gid)
CREATE UNIQUE INDEX link_attribute_type_idx_gid ON link_attribute_type (gid)
CREATE INDEX link_idx_type_attr ON link (link_type,attribute_count)
CREATE UNIQUE INDEX label_idx_gid ON label (gid)
CREATE INDEX label_idx_name ON label (name)
CREATE INDEX label_idx_sort_name ON label (sort_name)
CREATE INDEX label_idx_ipi_code ON label (ipi_code)
CREATE INDEX label_alias_idx_label ON label_alias (label)
CREATE UNIQUE INDEX label_alias_idx_locale_label ON label_alias (label,locale(32))
CREATE UNIQUE INDEX label_name_idx_name ON label_name (name(32))
DBD::mysql::db do failed: Duplicate entry 'Id Records' for key 'label_name_idx_name' at src/functions.pl line 96, line 213.
CREATE INDEX label_tag_idx_tag ON label_tag (tag)
CREATE INDEX label_tag_idx_label ON label_tag (label)
CREATE UNIQUE INDEX language_idx_iso_code_3b ON language (iso_code_3b)
CREATE UNIQUE INDEX language_idx_iso_code_3t ON language (iso_code_3t)
CREATE UNIQUE INDEX language_idx_iso_code_2 ON language (iso_code_2)
CREATE UNIQUE INDEX editor_collection_idx_gid ON editor_collection (gid)
CREATE INDEX editor_collection_idx_name ON editor_collection (name(32))
CREATE INDEX editor_collection_idx_editor ON editor_collection (editor)
CREATE UNIQUE INDEX medium_idx_release ON medium (release,position)
CREATE INDEX medium_idx_tracklist ON medium (tracklist)
CREATE UNIQUE INDEX puid_idx_puid ON puid (puid)
CREATE UNIQUE INDEX recording_idx_gid ON recording (gid)
CREATE INDEX recording_idx_name ON recording (name)
CREATE INDEX recording_idx_artist_credit ON recording (artist_credit)
CREATE UNIQUE INDEX recording_puid_idx_uniq ON recording_puid (recording,puid)
CREATE INDEX recording_puid_idx_puid ON recording_puid (puid)
CREATE INDEX recording_tag_idx_tag ON recording_tag (tag)
CREATE INDEX recording_tag_idx_recording ON recording_tag (recording)
CREATE UNIQUE INDEX release_idx_gid ON release (gid)
CREATE INDEX release_idx_name ON release (name)
CREATE INDEX release_idx_release_group ON release (release_group)
CREATE INDEX release_idx_artist_credit ON release (artist_credit)
CREATE INDEX release_idx_date ON release (date_year,date_month,date_day)
CREATE INDEX release_label_idx_release ON release_label (release)
CREATE INDEX release_label_idx_label ON release_label (label)
CREATE UNIQUE INDEX release_group_idx_gid ON release_group (gid)
CREATE INDEX release_group_idx_name ON release_group (name)
CREATE INDEX release_group_idx_artist_credit ON release_group (artist_credit)
CREATE INDEX release_group_tag_idx_tag ON release_group_tag (tag)
CREATE INDEX release_group_tag_idx_release_group ON release_group_tag (release_group)
CREATE UNIQUE INDEX release_name_idx_name ON release_name (name(32))
DBD::mysql::db do failed: Duplicate entry 'Great Pianists of the 20th Century, Volume 82: Sviatoslav Richte' for key 'release_name_idx_name' at src/functions.pl line 96, line 213.
CREATE UNIQUE INDEX script_idx_iso_code ON script (iso_code)
CREATE UNIQUE INDEX tag_idx_name ON tag (name)
CREATE INDEX track_idx_recording ON track (recording)
CREATE INDEX track_idx_tracklist ON track (tracklist,position)
CREATE INDEX track_idx_name ON track (name)
CREATE INDEX track_idx_artist_credit ON track (artist_credit)
CREATE UNIQUE INDEX track_name_idx_name ON track_name (name(32))
DBD::mysql::db do failed: Duplicate entry 'Pièces de Clavecin en concerts, Premier Concert: La Livri: Rond' for key 'track_name_idx_name' at src/functions.pl line 96, line 213.
CREATE INDEX tracklist_idx_track_count ON tracklist (track_count)
CREATE INDEX tracklist_index_idx ON tracklist_index (toc(32))
CREATE UNIQUE INDEX url_idx_gid ON url (gid)
CREATE UNIQUE INDEX url_idx_url ON url (url(32))
DBD::mysql::db do failed: Duplicate entry 'http://www.amazon.com/gp/product/B00004UZKL' for key 'url_idx_url' at src/functions.pl line 96, line 213.
CREATE UNIQUE INDEX work_idx_gid ON work (gid)
CREATE INDEX work_idx_name ON work (name)
CREATE INDEX work_idx_artist_credit ON work (artist_credit)
CREATE INDEX work_alias_idx_work ON work_alias (work)
CREATE UNIQUE INDEX work_alias_idx_locale_work ON work_alias (work,locale(32))
CREATE UNIQUE INDEX work_name_idx_name ON work_name (name(32))
DBD::mysql::db do failed: Duplicate entry 'Goldberg Variations, BWV 988: Variatio 5 a 1 ovvero 2 clav.' for key 'work_name_idx_name' at src/functions.pl line 96, line 213.
CREATE INDEX work_tag_idx_tag ON work_tag (tag)
CREATE INDEX annotation_id_idx ON annotation (id)
CREATE INDEX artist_id_idx ON artist (id)
CREATE INDEX artist_alias_id_idx ON artist_alias (id)
CREATE INDEX artist_credit_id_idx ON artist_credit (id)
CREATE INDEX artist_meta_id_idx ON artist_meta (id)
CREATE INDEX artist_name_id_idx ON artist_name (id)
CREATE INDEX artist_type_id_idx ON artist_type (id)
CREATE INDEX cdtoc_id_idx ON cdtoc (id)
CREATE INDEX clientversion_id_idx ON clientversion (id)
CREATE INDEX country_id_idx ON country (id)
CREATE INDEX currentstat_id_idx ON currentstat (id)
CREATE INDEX editor_id_idx ON editor (id)
CREATE INDEX editor_collection_id_idx ON editor_collection (id)
CREATE INDEX editor_preference_id_idx ON editor_preference (id)
CREATE INDEX editor_subscribe_artist_id_idx ON editor_subscribe_artist (id)
CREATE INDEX editor_subscribe_editor_id_idx ON editor_subscribe_editor (id)
CREATE INDEX editor_subscribe_label_id_idx ON editor_subscribe_label (id)
CREATE INDEX gender_id_idx ON gender (id)
CREATE INDEX historicalstat_id_idx ON historicalstat (id)
CREATE INDEX isrc_id_idx ON isrc (id)
CREATE INDEX l_artist_artist_id_idx ON l_artist_artist (id)
CREATE INDEX l_artist_label_id_idx ON l_artist_label (id)
CREATE INDEX l_artist_recording_id_idx ON l_artist_recording (id)
CREATE INDEX l_artist_release_id_idx ON l_artist_release (id)
CREATE INDEX l_artist_release_group_id_idx ON l_artist_release_group (id)
CREATE INDEX l_artist_url_id_idx ON l_artist_url (id)
CREATE INDEX l_artist_work_id_idx ON l_artist_work (id)
CREATE INDEX l_label_label_id_idx ON l_label_label (id)
CREATE INDEX l_label_recording_id_idx ON l_label_recording (id)
CREATE INDEX l_label_release_id_idx ON l_label_release (id)
CREATE INDEX l_label_release_group_id_idx ON l_label_release_group (id)
CREATE INDEX l_label_url_id_idx ON l_label_url (id)
CREATE INDEX l_label_work_id_idx ON l_label_work (id)
CREATE INDEX l_recording_recording_id_idx ON l_recording_recording (id)
CREATE INDEX l_recording_release_id_idx ON l_recording_release (id)
CREATE INDEX l_recording_release_group_id_idx ON l_recording_release_group (id)
CREATE INDEX l_recording_url_id_idx ON l_recording_url (id)
CREATE INDEX l_recording_work_id_idx ON l_recording_work (id)
CREATE INDEX l_release_group_release_group_id_idx ON l_release_group_release_group (id)
CREATE INDEX l_release_group_url_id_idx ON l_release_group_url (id)
CREATE INDEX l_release_group_work_id_idx ON l_release_group_work (id)
CREATE INDEX l_release_release_id_idx ON l_release_release (id)
CREATE INDEX l_release_release_group_id_idx ON l_release_release_group (id)
CREATE INDEX l_release_url_id_idx ON l_release_url (id)
CREATE INDEX l_release_work_id_idx ON l_release_work (id)
CREATE INDEX l_url_url_id_idx ON l_url_url (id)
CREATE INDEX l_url_work_id_idx ON l_url_work (id)
CREATE INDEX l_work_work_id_idx ON l_work_work (id)
CREATE INDEX label_id_idx ON label (id)
CREATE INDEX label_alias_id_idx ON label_alias (id)
CREATE INDEX label_meta_id_idx ON label_meta (id)
CREATE INDEX label_name_id_idx ON label_name (id)
CREATE INDEX label_type_id_idx ON label_type (id)
CREATE INDEX language_id_idx ON language (id)
CREATE INDEX link_id_idx ON link (id)
CREATE INDEX link_attribute_type_id_idx ON link_attribute_type (id)
CREATE INDEX link_type_id_idx ON link_type (id)
CREATE INDEX medium_id_idx ON medium (id)
CREATE INDEX medium_cdtoc_id_idx ON medium_cdtoc (id)
CREATE INDEX medium_format_id_idx ON medium_format (id)
CREATE INDEX puid_id_idx ON puid (id)
CREATE INDEX recording_id_idx ON recording (id)
CREATE INDEX recording_meta_id_idx ON recording_meta (id)
CREATE INDEX recording_puid_id_idx ON recording_puid (id)
CREATE INDEX release_id_idx ON release (id)
CREATE INDEX release_coverart_id_idx ON release_coverart (id)
CREATE INDEX release_group_id_idx ON release_group (id)
CREATE INDEX release_group_meta_id_idx ON release_group_meta (id)
CREATE INDEX release_group_type_id_idx ON release_group_type (id)
CREATE INDEX release_label_id_idx ON release_label (id)
CREATE INDEX release_meta_id_idx ON release_meta (id)
CREATE INDEX release_name_id_idx ON release_name (id)
CREATE INDEX release_packaging_id_idx ON release_packaging (id)
CREATE INDEX release_status_id_idx ON release_status (id)
CREATE INDEX replication_control_id_idx ON replication_control (id)
CREATE INDEX script_id_idx ON script (id)
CREATE INDEX script_language_id_idx ON script_language (id)
CREATE INDEX tag_id_idx ON tag (id)
CREATE INDEX track_id_idx ON track (id)
CREATE INDEX track_name_id_idx ON track_name (id)
CREATE INDEX tracklist_id_idx ON tracklist (id)
CREATE INDEX url_id_idx ON url (id)
CREATE INDEX work_id_idx ON work (id)
CREATE INDEX work_alias_id_idx ON work_alias (id)
CREATE INDEX work_meta_id_idx ON work_meta (id)
CREATE INDEX work_name_id_idx ON work_name (id)
CREATE INDEX work_type_id_idx ON work_type (id)
Done
Creating livestats tables and views... Done
Counting records for table ATE array_cat_agg(anyarray)... Done
Counting records for table annotation... Done
Counting records for table artist... Done
Counting records for table artist_alias... Done
(More counting......)
Counting records for table work_type... Done
Creating pendinglog table... Done
===== ALL DONE =====
When it says it can't create a UNIQUE index, it sucessfully created non-UNIQUE indexes.
Still, *_gid_redirect tables have no idexes, but cat replication/CreatePrimaryKeys.sql |grep gid_redirect shows that file sould have created them....... Other tables with gid fields do have UNIQUE indexes on them.
Other indexes are missing as well. For example, http://git.musicbrainz.org/gitweb/?p=musicbrainz-server.git;a=blob;f=admin/sql/CreatePrimaryKeys.sql;h=c07be640b6c48695f291a0740d187910ee6e5a0a;hb=master has a PK for artist_credit_name on (artist_credit, position) but the script does not create it.
link_attribute also has a missing key on (link,attribute_type)....
Around line 260 at backend/mysql.pl, we create indexes on columns named id, but I think the easiest soulution would be to run the CreatePrimaryKeys.sql provided by the dumps.
The reason the gid tables are missing an index is because they use foreign keys instead:
http://git.musicbrainz.org/gitweb/?p=musicbrainz-server.git;a=blob;f=admin/sql/CreateFKConstraints.sql#l76
I'm going to close this issue and open up another issue to discuss more generally if FKs should be implemented.
See https://github.com/chancemedia/mbzdb/issues/25