#   createindexes-mysql.sql
#   mysql script to create the indexes (helps with JOIN's and searches)
#   n6lhv@arrl.net (Wayne Smith)
#   created:  8-August-2004
#   updated:  10-August-2004



#   Display the MySQL version
SELECT version();



#   Manage the database
USE uls



#   Create all of indexes for all of the tables (*that we are using currently*)
# CP - Control Point
ALTER TABLE PUBACC_CP
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
      ADD INDEX(call_sign),
      ADD INDEX(state_code),
      ADD INDEX(control_county),
      ADD INDEX(control_city);

# EM - Emission
ALTER TABLE PUBACC_EM
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
      ADD INDEX(call_sign);

# EN - Entity
ALTER TABLE PUBACC_EN
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
      ADD INDEX(call_sign),
      ADD INDEX(state),
      ADD INDEX(city),
      ADD INDEX(entity_type);

# FR - Frequency
ALTER TABLE PUBACC_FR
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
      ADD INDEX(call_sign);

# HD - Application/Header
ALTER TABLE PUBACC_HD
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
      ADD INDEX(call_sign),
      ADD INDEX(grant_date),
      ADD INDEX(expired_date),
      ADD INDEX(cancellation_date),
      ADD INDEX(effective_date),
      ADD INDEX(last_action_date);

# LO - Location
ALTER TABLE PUBACC_LO
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
      ADD INDEX(call_sign),
      ADD INDEX(location_state),
      ADD INDEX(location_county),
      ADD INDEX(location_city);

# HS - History
ALTER TABLE PUBACC_HS
      ADD INDEX(unique_system_identifier),
      ADD INDEX(ULS_File_Number),
      ADD INDEX(callsign);


#   Create all of indexes for all of the tables (*that we might use in the future*)
# A2 - Application (Additional Data)
#ALTER TABLE PUBACC_A2
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# AC - Aircraft
#ALTER TABLE PUBACC_AC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# AD - Application/Detail
#ALTER TABLE PUBACC_AD
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# AM - Amateur
#ALTER TABLE PUBACC_AM
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(uls_file_num),
#      ADD INDEX(callsign);

# AN - Antenna
#ALTER TABLE PUBACC_AN
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# AS - Associated Callsign
#ALTER TABLE PUBACC_AS
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(callsign);

# AT - Attachments
#ALTER TABLE PUBACC_AT
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# BC - Broadcast Callsign
#ALTER TABLE PUBACC_BC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(callsign),
#      ADD INDEX(broadcast_state),
#      ADD INDEX(broadcast_city);

# BF - Buildout Frequency
#ALTER TABLE PUBACC_BF
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(call_sign);

# BL - Buildout Location
#ALTER TABLE PUBACC_BL
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(call_sign);

# BO - Buildout
#ALTER TABLE PUBACC_BO
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(call_sign);

# CF - Callsign/Filenumber
#ALTER TABLE PUBACC_CF
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# CG - Coast and Ground
#ALTER TABLE PUBACC_CG
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# CO - Comments
#ALTER TABLE PUBACC_CO
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Num),
#      ADD INDEX(callsign);

# CS - COSER
#ALTER TABLE PUBACC_CS
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# F2 - Additional Frequency Information
#ALTER TABLE PUBACC_F2
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# FA - FRC Administration
#ALTER TABLE PUBACC_FA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# FC - Frequency Coordination
#ALTER TABLE PUBACC_FC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# FF - Frequency Free Form (Special Condition)
#ALTER TABLE PUBACC_FF
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(callsign);

# FS - Frequency (Special Condition)
#ALTER TABLE PUBACC_FS
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(call_sign);

# FT - Frequency Type
#ALTER TABLE PUBACC_FT
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# IA - International Address
#ALTER TABLE PUBACC_IA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign),
#      ADD INDEX(international_city);

# IR - IRAC
#ALTER TABLE PUBACC_IR
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# L2 - License (Additional Data)
#ALTER TABLE PUBACC_L2
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# LA - License Attachment
#ALTER TABLE PUBACC_LA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(callsign);

# LF - Location Free Form (Special Condition)
#ALTER TABLE PUBACC_LF
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(callsign);

# LM - License Mobile Administration
#ALTER TABLE PUBACC_LM
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(callsign);

# LS - Location (Special Condition)
#ALTER TABLE PUBACC_LS
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(call_sign);

# MC - Market Coordinate
#ALTER TABLE PUBACC_MC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# MF - Market Frequency
#ALTER TABLE PUBACC_MF
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# MI - MDS/ITFS Administration
#ALTER TABLE PUBACC_MI
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# MK - Market
#ALTER TABLE PUBACC_MK
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# MP - Market Partition
#ALTER TABLE PUBACC_MP
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# MW - Microwave
#ALTER TABLE PUBACC_MW
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# OP - Area of Operation Text
#ALTER TABLE PUBACC_OP
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(callsign);

# PA - Microwave Path
#ALTER TABLE PUBACC_PA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(callsign);

# PC - Points of Communication
#ALTER TABLE PUBACC_PC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign),
#      ADD INDEX(state),
#      ADD INDEX(city);

# RA - Radial
#ALTER TABLE PUBACC_RA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# RC - Receiver
#ALTER TABLE PUBACC_RC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# RE - Reason
#ALTER TABLE PUBACC_RE
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# RZ - Receive Zone
#ALTER TABLE PUBACC_RZ
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# SC - Special Condition
#ALTER TABLE PUBACC_SC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(call_sign);

# SE - Ship Exemption
#ALTER TABLE PUBACC_SE
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# SF - License Free Form (Special Condition)
#ALTER TABLE PUBACC_SF
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(callsign);

# SG - Microwave Segments
#ALTER TABLE PUBACC_SG
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# SH - Ship
#ALTER TABLE PUBACC_SH
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(callsign);

# SI - SIDS
#ALTER TABLE PUBACC_SI
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# SV - Ship Voyage
#ALTER TABLE PUBACC_SV
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# TA - Transfer/Assign
#ALTER TABLE PUBACC_TA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);

# TL - Tribal Land
#ALTER TABLE PUBACC_TL
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# UA - Cellular Unserved Area
#ALTER TABLE PUBACC_UA
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number),
#      ADD INDEX(call_sign);

# VC - Vanity Callsign
#ALTER TABLE PUBACC_VC
#      ADD INDEX(unique_system_identifier),
#      ADD INDEX(ULS_File_Number);