Which optimizations should I employ to make this DB manageable? I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are suitable for my setup.
I loaded about 90 mil rows from flatfile csvs into a mysql database (aws RDS, if that matters). There are 60 columns and each row is a click on a website.
Datatypes: All datatypes are text because of limitations with the mysqlimport utility (specifically, its treatment of null vs. 0 values in numerical fields, which i could be wrong about)
Each column falls into one of these categories:
Visit info: time, referrer, etc. Many pages log millions of visits (front page, for ex.), some log only a few.
User info: city/state, etc. Users log anywhere from 1 to thousands of visits, with the distribution skewing right.
Page info: url, content flags, etc.
IO - The database only has 4 users, so will not have a lot of i/o, mostly just queries to populate dataframes in our python data analysis environment.
indexes - none besides the default generated index. Candidates for indexing are user ID and page view timestamp (potentially a multi column index?)
I welcome any other ideas, but this is what I've come up w/ with some searching. Interested to hear which steps are worthwhile and which aren't.
right-size the datatype of each field -- does this offer performance boost? most fields are text or boolean flags.
break table into at least 2 others, e.g., visit info and page info.
Keep it in one table and partition it. From what I've read, I think partitioning on the most frequently queried field is the optimal choice.