WackoWiki

View source for optimize log_table for bad_behavior

%%
"CREATE TABLE IF NOT EXISTS `$name_escaped` (
		`log_id` INT(11) NOT NULL auto_increment,
		`ip` VARCHAR(45) NOT NULL,
		`host` VARCHAR(255) NOT NULL,
		`date` DATETIME NOT NULL default '0000-00-00 00:00:00',
		`request_method` VARCHAR(8) NOT NULL,
		`request_uri` VARCHAR(2083) NOT NULL,
		`server_protocol` VARCHAR(12) NOT NULL,
		`http_headers` TEXT NOT NULL,
		`user_agent` TEXT NOT NULL,
		`user_agent_hash` CHAR(40) NOT NULL,
		`request_entity` TEXT NOT NULL,
		`status_key` VARCHAR(10) NOT NULL,
		PRIMARY KEY (`log_id`),
		KEY `idx_staus_key` (`status_key`),
		KEY `idx_user_agent_hash` (`user_agent_hash`),
		KEY `idx_ip` (`ip`),
		KEY `idx_request_method` (`request_method`)
		);";	// TODO: INDEX might need tuning
%%

https://php.net/manual/en/reserved.variables.server.php

  1. ##ip## TEXT -> 
    2. VARCHAR(45)
    3. https://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address
    1. add field ##host## to avoid random lookups over and over again -> VARCHAR(255)
      2. https://stackoverflow.com/questions/8717378/what-is-the-maximum-length-of-an-idna-converted-domain-name
  2. ##request_method## TEXT -> 
    3. VARCHAR (8)
    3. 'GET', 'HEAD', 'POST', 'PUT'
  3. ##request_uri## TEXT -> 
    1. VARCHAR(2083)
    4. https://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers -> de facto limit of 2000 characters
    5. https://stackoverflow.com/questions/219569/best-database-field-type-for-a-url#
  4. ##server_protocol## TEXT -> 
    5.  VARCHAR (10)
    5. 'HTTP/1.0', 'HTTP/1.1'
  5. ##http_headers## TEXT -> 
  6. ##user_agent## TEXT -> 
    7. https://stackoverflow.com/questions/654921/how-big-can-a-user-agent-string-get
    8. Have another UNIQUE BINARY(32) (or 64, or 128 depending on your hash length) and hash the UserAgent
    9. ##user_agent_hash## -> CHAR(40)
  7. ##request_entity## TEXT -> 
  8. ##key## TEXT -> 
    9. VARCHAR (8)
    10. 'key' is reserved word -> rename to  'status_key'