- Task: Database Revision
- Legacy System: LAMP (MySql v5.5)
- Current System: Node Express MySql v8.023
Data is acquired from 14 sources as .txt files with different delimiters and imported to their own child table. Only SELECT statements are used to query data.
Each of the 14 child tables have:
Associated data type that are normally not an INT; Between 8 and 92 columns of data. Between 4 and 24 foreign key relationships with parent tables normally not using an INT for PRIMARY KEY.
In Legacy version PhpMyAdmin was used allowing display of data from parent table when using simple SELECT statement on child table with foreign key.
Current development team anticipates problems with not using INT for PRIMARY KEY
Not sure if current configuration would be considered a staging environment and how to determine best practices to enhance to a production environment?
Any explanation or suggestion research topics are appreciated.
Table_1 stores data including address information that is imported from fixed width .txt document.
CREATE TABLE table_1 ( strno INT NULL, strfrac VARCHAR(255) NULL, strdir VARCHAR(255) NULL, strname VARCHAR(255) NULL, strtype VARCHAR(255) NULL, strunit VARCHAR(255) NULL, city VARCHAR(255) NULL
3 of the 8 columns require translation before delivery to user including:
strdir strtype city
For these 3 columns 3 separate tables have been created to include:
- Code for all possible values in table_1;
- Translated name for display to User;
- Description for display to User;
table strdirs records
'N','North','North is a valid direction' 'S','South','South is a valid direction' 'E','East','East is a valid direction' 'W','West','West is a valid direction' 'NE','Northeast','Northeast is a valid direction' 'NW','Northwest','Northwest is a valid direction' 'SE','Southeast','Southeast is a valid direction' 'SW','Southwest','Southwest is a valid direction'
table strtypes records
'ALLY','Alley','An Alley is a valid street type for a location address' 'ANX','Annex','An Annex is a valid street type for a location address' 'AVE','Avenue','An Avenue is a valid street type for a location address' 'BEND','Bend','A Bend is a valid street type for a location address' 'BLVD','Boulevard','A Boulevard is a valid street type for a location address' 'BYP','Bypass','A Bypass is a valid street type for a location address' 'CYN','Canyon','A Canyon is a valid street type for a location address' 'CIR','Circle','A Circle is a valid street type for a location address'
table cities records
'BC','Boulder City','Boulder City is a beautiful city in southern Nevada' 'BUNK','Bunkerville','Bunkerville is a beautiful city in southern Nevada' 'CNTY','County','County is a beautiful city in southern Nevada' 'ENT','Enterprise','Enterprise is a beautiful city in southern Nevada' 'GLEN','Glendale','Glendale is a beautiful city in southern Nevada' 'HEND','Henderson','Henderson is a beautiful city in southern Nevada' 'ISPR','Indian Springs','Indian Springs is a beautiful city in southern Nevada' 'LAUG','Laughlin','Laughlin is a beautiful city in southern Nevada'
Table_1 has foreign key relationships with 3 tables
strdirs - ALTER TABLE table_1 ADD FOREIGN KEY (strdir) REFERENCES strdirs(id); strtypes - ALTER TABLE table_1 ADD FOREIGN KEY (strtype) REFERENCES strtypes(id); cities - ALTER TABLE table_1 ADD FOREIGN KEY (city) REFERENCES cities(id);
By 'staging environment' I was picturing a preliminary environment used to get all data into one place and then transform that data using the parent tables and load into a new productions environment. It would be great if the most efficient methods could be determined for converting the 'N' in table_1 to 'North' or 'North is a valid street direction' (the values in strdirs table) Some of the other parent table codes are less obviously related to the value contained in table_1 or other child tables. The values in the parent tables have been constant over the last decade and not likely to require edits.
The first column is the primary key. I can include the create table and insert statements if it would help. I will go back through and update the size max. Previously it looked like a safe size for matching relationships and thought VARCHAR only reserved the amount of space actually used so thought it wouldn't have negative affect.
INTas the primary key isn't necessarily bad but can become an issue if your primary key / indexed fields are large data types, for example if your index for
(strfrac, strdir, strname), a 765 byte index per row, then that could cause some noticeable performance issues. Anyway you should add some specific details to help us focus in on your performance issues, such as some example slow queries, their average run times, and the
EXPLAIN ANALYZEoutput of them.