When I say efficient, I mean total storage(kb), and not excessively burdening the CPU.
I have 70 columns of data. The last 35 fields have always been NULL.
- The table has only 4 columns of completely unique data.
- The rest of the fields have only few values.
- One fields has only GET,POST and their are 4-5 other values that never occur.
- The columns are 69 varchar, bigint,int, and 2 blob.(I am open to change) The blobs are text, but contain too many different characters set, or random junk from hackers. So rather than figuring it out, binary and done.
Most of the other columns have 4-5 distinct() values per column
At what point does a lookup column make sense? Another table with ID Value, and only the ID is present in the first table. For example if you have GET,POST by the time you add overhead for lookup does it make sense to even do this.
Should I use an enum or etc?
The table will eventually hold 100,000+ rows.
How many bytes in the value fields to actually save space before the whole lookup makes sense?
What is the best way to handle the 35 blank columns in terms of storage? Create a separate table with a single ID, have the 35 columns with 1 row that has all NULL. Then just say ID 1, and if a different arrangement ever comes along then it is ID 2?
One column is always "Apache", another "keep-alive","closed",NULL. Encoding is "gzip, deflate, sdch","identity, gzip, deflate","deflate, gzip","gzip,deflate,br","gzip,deflate","gzip, deflate, sdch, identity","gzip","identity",NULL,"x-gzip, gzip, deflate","gzip","deflate"
Answering questions:
How is that table used?
backend to apache via php. I will be looking for patterns, and marking them with a score indicating bad to good probability.
How do you query the database?
Mainly via php(prepare), probably put a web gui on it.
When you perform queries that use this table: Do you need all the values for all the columns?
No, certainly not the 35 blank columns.
9 of the columns always have the same 1 record, and I will only care if they are different. Maybe I will setup up a trigger, if they are ever different, but probably otherwise ignore those columns.
So 70 -35 -9 = 26 or less frequently queried columns.
Column Type Comment REDIRECT_UNIQUE_ID varchar(50) NULL REDIRECT_REQUEST_METHOD varchar(20) NULL REDIRECT_STATUS int(11) NULL UNIQUE_ID varchar(50) NULL HTTP_HOST varchar(30) NULL HTTP_USER_AGENT varchar(300) NULL HTTP_ACCEPT varchar(300) NULL HTTP_ACCEPT_LANGUAGE varchar(120) NULL HTTP_ACCEPT_ENCODING varchar(60) NULL HTTP_COOKIE blob NULL HTTP_CONNECTION varchar(30) NULL HTTP_UPGRADE_INSECURE_REQUESTS varchar(10) NULL HTTP_CACHE_CONTROL varchar(50) NULL PATH varchar(300) NULL SERVER_SIGNATURE varchar(300) NULL SERVER_SOFTWARE varchar(20) NULL SERVER_NAME varchar(50) NULL SERVER_ADDR varchar(30) NULL SERVER_PORT varchar(5) NULL REMOTE_ADDR bigint(40) NULL DOCUMENT_ROOT varchar(20) NULL REQUEST_SCHEME varchar(20) NULL CONTEXT_PREFIX varchar(300) NULL CONTEXT_DOCUMENT_ROOT varchar(50) NULL SERVER_ADMIN varchar(40) NULL SCRIPT_FILENAME varchar(110) NULL REMOTE_PORT int(11) NULL REDIRECT_URL blob NULL GATEWAY_INTERFACE varchar(15) NULL SERVER_PROTOCOL varchar(170) NULL REQUEST_METHOD varchar(20) NULL QUERY_STRING varchar(123) NULL REQUEST_URI blob NULL SCRIPT_NAME varchar(100) NULL PHP_SELF varchar(75) NULL REQUEST_TIME_FLOAT decimal(15,4) NULL REQUEST_TIME int(15) unsigned NULL HTTP_PRAGMA varchar(300) NULL LESSKEY varchar(300) NULL NNTPSERVER varchar(300) NULL MANPATH varchar(300) NULL XDG_SESSION_ID varchar(300) NULL HOSTNAME varchar(300) NULL XKEYSYMDB varchar(300) NULL HOST varchar(300) NULL TERM varchar(300) NULL SHELL varchar(300) NULL PROFILEREAD varchar(300) NULL HISTSIZE varchar(300) NULL SSH_CLIENT varchar(300) NULL MORE varchar(300) NULL SSH_TTY varchar(300) NULL USER varchar(300) NULL LS_COLORS varchar(300) NULL XNLSPATH varchar(300) NULL HOSTTYPE varchar(300) NULL CONFIG_SITE varchar(300) NULL FROM_HEADER varchar(300) NULL PAGER varchar(300) NULL CSHEDIT varchar(300) NULL XDG_CONFIG_DIRS varchar(300) NULL LIBGL_DEBUG varchar(300) NULL MINICOM varchar(300) NULL MAIL varchar(300) NULL CPU varchar(300) NULL INPUTRC varchar(300) NULL PWD varchar(300) NULL LANG varchar(300) NULL PYTHONSTARTUP varchar(300) NULL GPG_TTY varchar(300) NULL SHLVL varchar(300) NULL HTTP_USER_AGENT_I bigint(20) NULL SSH_CONNECTION varchar(300) NULL LESSCLOSE varchar(300) NULL WINDOWMANAGER varchar(300) NULL REDIRECT_spider varchar(300) NULL spider varchar(300) NULL CONTENT_TYPE varchar(50) NULL HTTP_REFERER varchar(50) NULL HTTP_ACCEPT_CHARSET varchar(75) NULL HTTP_DNT varchar(10) NULL
TINYINT
orSMALLINT
. When you query the database, don't make aGET
for every lookup. Just use aSELECT
with as manyJOINs
as necessary.